package com.lc.service.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.aliyuncs.CommonRequest;
import com.aliyuncs.CommonResponse;
import com.aliyuncs.DefaultAcsClient;
import com.aliyuncs.IAcsClient;
import com.aliyuncs.http.MethodType;
import com.aliyuncs.profile.DefaultProfile;
import com.cnpc.framework.base.entity.BaseResponse;
import com.cnpc.framework.base.entity.Dict;
import com.cnpc.framework.base.entity.SysFile;
import com.cnpc.framework.base.entity.User;
import com.cnpc.framework.base.pojo.TreeNode;
import com.cnpc.framework.base.service.DictService;
import com.cnpc.framework.base.service.FunctionService;
import com.cnpc.framework.base.service.OrgService;
import com.cnpc.framework.base.service.impl.BaseServiceImpl;
import com.cnpc.framework.utils.*;
import com.google.gson.Gson;
import com.lc.entity.*;
import com.lc.service.AppService;
import com.lc.service.UtilService;
import com.lc.utils.Excel07ExportUtil;
import com.wf.captcha.base.Captcha;
import net.sourceforge.pinyin4j.PinyinHelper;
import net.sourceforge.pinyin4j.format.HanyuPinyinCaseType;
import net.sourceforge.pinyin4j.format.HanyuPinyinOutputFormat;
import net.sourceforge.pinyin4j.format.HanyuPinyinToneType;
import net.sourceforge.pinyin4j.format.exception.BadHanyuPinyinOutputFormatCombination;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.OutputStream;
import java.util.*;

@Service
public class AppServiceImpl extends BaseServiceImpl implements AppService {

    private Logger logger = LoggerFactory.getLogger(getClass());

    @Autowired
    DictService dictService;

    @Autowired
    FunctionService functionService;

    @Autowired
    OrgService orgService;

    @Autowired
    UtilService utilService;

    @Override
    public BaseResponse saveChildren(String parentId, String type, List<String> names) throws Exception{
        BaseResponse response = new BaseResponse();
        String tableName = "tbl_dict";
        if ("1".equals(type)) {
            tableName = "tbl_org";
        } else if ("2".equals(type)) {
            tableName = "tbl_function";
        }

        String sql = "select max(levelCode) from " + tableName + " where parent_id = :parentId";
        Map<String, Object> params = new HashMap<>();
        params.put("parentId", parentId);
        String maxLC = getBySql(sql, params);

        int baseCode = StringUtils.isEmpty(maxLC) ? 0 : Integer.parseInt(maxLC.substring(maxLC.length() - 6));

        sql = "select levelCode from " + tableName + " where id = :parentId";
        String pLevelCode = getBySql(sql, params);


        sql = "insert into " + tableName +
                "  (id,version,deleted,create_date_time,name,code,levelCode,parent_id) values " +
                "   (replace(UUID(),'-',''),0,0,NOW(),:name,:code,:levelCode,:parentId)";
        for (int i = 0; i < names.size(); i++) {
            params.put("name", names.get(i));
            params.put("code", pLevelCode + String.format("%06d", (baseCode + i + 1)));
            params.put("levelCode", pLevelCode + String.format("%06d", (baseCode + i + 1)));
            executeSql(sql, params);
        }

        response.setStatus(0);

        return response;
    }
    @Override
    public List<Dict> getDictsByCode(String code) throws Exception {
        //return dictService.getDictsByCode(code);

        String hql = "from Dict where code=:code and (deleted = 0 or deleted is null)";
        Map<String, Object> params = new HashMap<>();
        params.put("code", code);
        Dict dict = this.get(hql, params);
        if (dict == null) {
            return null;
        }
        List<Dict> dicts = this.find("from Dict where parentId='" + dict.getId() + "' and (deleted = 0 or deleted is null)  order by levelCode");
        return dicts;
    }

    @Override
    public BaseResponse loginByPwd(String loginName, String password) throws Exception {
        String sql = "select u.*,o.name deptName,o.org_type orgType from tbl_user u" +
                " left join tbl_org o on u.dept_id = o.id where login_name= :loginName ";
        Map<String, Object> params = new HashMap<>();
        params.put("loginName", loginName);


        Map<String, Object> user = getMapBySql(sql, params);

        BaseResponse response = new BaseResponse();
        if (user == null) {
            response.setStatus(1);
            response.setMessage("用户名或密码错误");
            return response;
        }

        password = EncryptUtil.getPassword(password, loginName);
        String oldPwd = (String) user.get("password");
        if (!password.equals(oldPwd)) {
            response.setStatus(1);
            response.setMessage("用户名或密码错误");
            return response;
        }
        String id = (String) user.get("id");
        String rfsql = "select count(*) from tbl_role_function rf " +
                "left join tbl_function f on rf.functionId=f.id " +
                "left join tbl_user_role ur on rf.roleId=ur.roleId " +
                "where ur.userId=:id and f.id is not null";
        Map<String, Object> rfparams = new HashMap<>();
        rfparams.put("id", id);
        Long rcnt = countBySql(rfsql, rfparams);

        if (rcnt == null || rcnt <= 0) {
            response.setStatus(1);
            response.setMessage("权限不足");
            return response;
        } else {
            sql = "select r.id,r.name,r.code from tbl_user_role ur left join tbl_role r on ur.roleId=r.id " +
                    " where ur.userId=:userid order by r.sort";
            Map<String, Object> params2 = new HashMap<>();
            params2.put("userid", id);
            List<Map<String, Object>> roleList = findMapBySql(sql, params2);
            user.put("roleList", roleList);
        }
        String token = UUID.randomUUID().toString().replace("-", "");
        Calendar calendar = Calendar.getInstance();
        calendar.add(Calendar.DATE, 30);
        String tokenLimit = DateUtil.format(calendar.getTime(), DateUtil.YYYY_MM_DD_HH_MM_SS);
        sql = " update tbl_user set token = :token ,token_limit = :token_limit where id = :id";
        Map<String, Object> params1 = new HashMap<>();
        params1.put("token", token);
        params1.put("token_limit", tokenLimit);
        params1.put("id", user.get("id"));
        executeSql(sql, params1);
        user.put("token", token);

        response.setStatus(0);
        response.setData(user);
        logger.error("111111111111111111");
        return response;
    }

    @Override
    public BaseResponse getImageCode() throws Exception {
        BaseResponse baseResponse = new BaseResponse();
        // 获取运算的结果
        LoginProperties loginProperties = new LoginProperties();
        Captcha captcha = loginProperties.getCaptcha();
        String uuid = UUID.randomUUID().toString().replace("-", "");
        //当验证码类型为 arithmetic时且长度 >= 2 时，captcha.text()的结果有几率为浮点型
        String captchaValue = captcha.text();
        System.out.println("uuid:" + uuid);
        System.out.println("captchaValue:" + captchaValue);
        if (captcha.getCharType() - 1 == LoginCodeEnum.arithmetic.ordinal() && captchaValue.contains(".")) {
            captchaValue = captchaValue.split("\\.")[0];
        }
        System.out.println("captchaValue:" + captchaValue);
        // 保存
        redisDao.add(uuid, 300, captchaValue);
        // 验证码信息
        Map<String, Object> imgResult = new HashMap<String, Object>(2) {{
            put("img", captcha.toBase64());
            put("uuid", uuid);
        }};
        baseResponse.setData(imgResult);
        return baseResponse;
    }

    @Override
    public BaseResponse checkImageCode(String uuid, String code) throws Exception {
        BaseResponse baseResponse = new BaseResponse();
        System.out.println("uuid:" + uuid);
        System.out.println("code:" + code);
        String oldCode = redisDao.get(uuid);
        System.out.println("captchaValue:" + oldCode);
        redisDao.delete(uuid);
        if (StringUtils.isEmpty(oldCode)) {
            baseResponse.setStatus(1);
            baseResponse.setMessage("验证码不存在或已过期");
        }
        if (StringUtils.isEmpty(code) || !code.equalsIgnoreCase(oldCode)) {
            baseResponse.setStatus(1);
            baseResponse.setMessage("验证码错误");
        }
        return baseResponse;
    }

    @Override
    public BaseResponse login(String mobile, String code, String password, String token) throws Exception {
        BaseResponse response = new BaseResponse();
        String sql = "select u.*,o.name deptName,o.org_type orgType from tbl_user u " +
                " left join tbl_org o on u.dept_id = o.id where ";
        Map<String, Object> params = new HashMap<>();
        if (StrUtil.isNotEmpty(token)) {
            sql += " u.token = :token and u.token_limit >=now() ";
            params.put("token", token);
            response.setMessage("登录超时，请重新登录！");
        } else if (StrUtil.isNotEmpty(password)) {
            sql += " u.login_name = :mobile and u.password = :password ";
            params.put("mobile", mobile);
            params.put("password", EncryptUtil.getPassword(password, mobile));
            response.setMessage("用户名或密码错误");
        } else if (StrUtil.isNotEmpty(code)) {
            response = checkCode(mobile, code);
            if (response.getStatus() != 0) {
                return response;
            }
            sql += " u.mobile = :mobile ";
            params.put("mobile", mobile);
            response.setMessage("用户名或验证码错误！");
        } else {
            response = new BaseResponse();
            response.setStatus(1);
            response.setMessage("参数缺失！");
            return response;
        }

        Map<String, Object> user = getMapBySql(sql, params);
        if (user == null) {
            response.setStatus(1);
            return response;
        }


        if (StringUtils.isEmpty(token)) {
            token = UUID.randomUUID().toString().replace("-", "");
            Calendar calendar = Calendar.getInstance();
            calendar.add(Calendar.DATE, 30);
            String tokenLimit = DateUtil.format(calendar.getTime(), DateUtil.YYYY_MM_DD_HH_MM_SS);
            sql = " update tbl_user set token = :token ,token_limit = :token_limit where id = :id";
            Map<String, Object> params1 = new HashMap<>();
            params1.put("token", token);
            params1.put("token_limit", tokenLimit);
            params1.put("id", user.get("id"));
            executeSql(sql, params1);
            user.put("token", token);
        }

        response.setData(user);
        response.setMessage(null);
        return response;
    }

    @Override
    public BaseResponse checkToken(String token) throws Exception {
        BaseResponse response = new BaseResponse();
        String sql = "select u.id,u.mobile,u.deptId from app_user u  where u.token = :token and u.tokenLimit >=now() ";
        Map<String, Object> params = new HashMap<>();
        params.put("token", token);
        Map<String, Object> user = getMapBySql(sql, params);
        if (user == null) {
            logger.error("dadsadas");
            response.setStatus(201);
            response.setMessage("登录超时，请重新登录！");
            return response;
        }
        response.setData(user);
        response.setMessage(null);
        return response;
    }

    @Override
    public BaseResponse checkEmpToken(String token) throws Exception {
        BaseResponse response = new BaseResponse();
        String sql = "select u.id,u.name,u.dept_id,o.name deptName,o.org_type orgType from tbl_user u left join tbl_org o on u.dept_id = o.id where u.token = :token and u.token_limit >=now() ";
        Map<String, Object> params = new HashMap<>();
        params.put("token", token);
        Map<String, Object> user = getMapBySql(sql, params);
        if (user == null) {
            response.setStatus(201);
            response.setMessage("登录超时，请重新登录！");
            return response;
        }
        response.setData(user);
        response.setMessage(null);
        return response;
    }

    @Override
    public BaseResponse checkTokenAndRoleCodes(String token) throws Exception {
        BaseResponse baseResponse = checkEmpToken(token);
        if (baseResponse.getStatus() != 0) {
            return baseResponse;
        }
        Map<String, Object> user = (Map<String, Object>) baseResponse.getData();
        String empId = user.get("id").toString();
        String sql = "select group_concat(r.code) from tbl_role r left join tbl_user_role ur on ur.roleId = r.id where ur.userId = :userId";
        Map<String, Object> params = new HashMap<>();
        params.put("userId", empId);
        String roleCodes = getBySql(sql, params);
        user.put("roleCodes", roleCodes);

        return baseResponse;
    }

    @Override
    public BaseResponse generateCode(String mobile,String type) throws Exception {
        BaseResponse response = new BaseResponse();
        String cntsql = "select * from app_user where mobile = :mobile";
        Map<String, Object> params = new HashMap<>();
        params.put("mobile", mobile);
        AppUser appUser = getBySql(cntsql, params,AppUser.class);
        if ("1".equals(type)) {
            if (appUser !=null) {
                response.setStatus(1);
                response.setMessage("该用户已存在，如果忘记密码请点击修改密码");
                return response;
            }
        }else if("0".equals(type)){
            if (appUser == null) {
                response.setStatus(1);
                response.setMessage("该用户不存在");
                return response;
            }

        }

        String sql = "select * from tbl_sms_code where mobile = :mobile ";

        SmsCode smscode = getBySql(sql, params, SmsCode.class);
        if (smscode == null) {

            smscode = new SmsCode();
            smscode.setMobile(mobile);
            save(smscode);

        }
        String code = CodeUtil.getRandom(6);

        DefaultProfile profile = DefaultProfile.getProfile("default", "LTAILUtsrCKhNcmZ", "gS93g2WRftmFsU7UOyukUuBvJkP2Zt");
        IAcsClient client = new DefaultAcsClient(profile);

        CommonRequest request = new CommonRequest();
        request.setMethod(MethodType.POST);
        request.setDomain("dysmsapi.aliyuncs.com");
        request.setVersion("2017-05-25");
        request.setAction("SendSms");
        request.putQueryParameter("RegionId", "default");
        request.putQueryParameter("PhoneNumbers", mobile);
        request.putQueryParameter("SignName", "创赢双闸");
        request.putQueryParameter("TemplateCode", "SMS_150505309");
        request.putQueryParameter("TemplateParam", "{ \"code\":\"" + code + "\"}");

        CommonResponse commonResponse = client.getCommonResponse(request);
        String data = commonResponse.getData();
        System.out.println(data);
        JSONObject obj = JSON.parseObject(data);
        String resCode = obj.getString("Code");
        String message = obj.getString("Message");
       /* String resCode = "OK";
        String message = "OK";*/
        if ("OK".equals(resCode)) {
            String updateSql = " update tbl_sms_code set temp_code= :code , temp_code_limit = date_add(now(), interval 5 minute) where mobile = :mobile";
            Map<String, Object> updateParams = new HashMap<>();
            updateParams.put("mobile", mobile);
            updateParams.put("code", code);
            executeSql(updateSql, updateParams);

            response.setStatus(0);
        } else {
            response.setStatus(1);
            response.setMessage(message);
        }


        return response;
    }


    @Override
    public BaseResponse checkAppUserMobile(String mobile) throws Exception {
        BaseResponse response = new BaseResponse();

        String sql = "select su.*  from app_user su  where su.mobile = :mobile ";
        Map<String, Object> params = new HashMap<>();
        params.put("mobile", mobile);

        AppUser user = getBySql(sql, params, AppUser.class);
        if (user == null) {
            response.setStatus(1);
            response.setMessage("该用户不存在");
            return response;
        }
        String token = UUID.randomUUID().toString().replace("-", "");
        Calendar calendar = Calendar.getInstance();
        calendar.add(Calendar.DATE, 30);
        String tokeLimit = DateUtil.format(calendar.getTime(), DateUtil.YYYY_MM_DD_HH_MM_SS);
        user.setToken(token);
        user.setTokenLimit(tokeLimit);

        update(user);

        Map<String, Object> data = new HashMap<>();
        data.put("token", token);
        data.put("mobile", mobile);
        data.put("type", user.getType());

        response.setData(data);
        return response;
    }

    @Override
    public BaseResponse addOrUpdateAppUser240925(String mobile, String deptName) throws Exception {
        BaseResponse response = new BaseResponse();

        String sql= "select id from tbl_org where name = :deptName";
        Map<String, Object> params = new HashMap<>();
        params.put("deptName", deptName);
        String deptId = getBySql(sql,params);
        if (StringUtils.isEmpty(deptId)) {
            response.setStatus(1);
            response.setMessage("该部门不存在");
            return response;
        }

        sql = "select * from tbl_dict where code = :code order by levelCode asc limit 1";
        params.clear();
        params.put("code","wjxz");
        Dict cfgDict = getBySql(sql, params, Dict.class);
        if (cfgDict == null || StringUtils.isEmpty(cfgDict.getValue())) {
            response.setStatus(1);
            response.setMessage("问卷不存在！");
            return response;
        }
        String wjCode = cfgDict.getValue();
        params.put("code",wjCode);
        Dict rootDict = getBySql(sql, params, Dict.class);
        if (rootDict == null) {
            response.setStatus(1);
            response.setMessage("问卷不存在！");
            return response;
        }

        String startTime = rootDict.getValuex();
        String endTime = rootDict.getValuey();
        String curTime = DateUtil.format(new Date(),DateUtil.YYYY_MM_DD_HH_MM_SS);

        if (curTime.compareTo(startTime)<0 || curTime.compareTo(endTime)>0) {
            response.setStatus(1);
            response.setMessage("不在提交时间范围！");
            return response;
        }

        String token = UUID.randomUUID().toString().replace("-", "");
        Calendar calendar = Calendar.getInstance();
        calendar.add(Calendar.DATE, 30);
        String tokeLimit = DateUtil.format(calendar.getTime(), DateUtil.YYYY_MM_DD_HH_MM_SS);

        sql = "select su.*  from app_user su  where su.mobile = :mobile ";
        params.clear();
        params.put("mobile", mobile);

        AppUser user = getBySql(sql, params, AppUser.class);
        if (user == null) {
            user = new AppUser();
            user.setDefaultTime();
            user.setMobile(mobile);
            user.setDeptName(deptName);
            user.setDeptId(deptId);
            user.setToken(token);
            user.setTokenLimit(tokeLimit);
            save(user);
        }else {
            sql = "select * from app_wj_result where wjCode = :code and userId = :userId";
            params.clear();
            params.put("code",wjCode);
            params.put("userId",user.getId());
            AppWjResult oldWjResult = getBySql(sql, params, AppWjResult.class);
            if (oldWjResult != null) {
                response.setStatus(1);
                response.setMessage("您已参与过本次问卷！");
                return response;
            }
            user.setToken(token);
            user.setTokenLimit(tokeLimit);

            if (!deptId.equals(user.getDeptId())) {
                user.setDeptName(deptName);
                user.setDeptId(deptId);
            }

            update(user);
        }

        Map<String, Object> data = new HashMap<>();
        data.put("token", token);
        data.put("mobile", mobile);
        data.put("type", user.getType());

        response.setData(data);
        return response;
    }

    @Override
    public BaseResponse addOrUpdateAppUser240929(String mobile, String deptId) throws Exception {
        BaseResponse response = new BaseResponse();

        String sql= "select name from tbl_org where id = :deptId";
        Map<String, Object> params = new HashMap<>();
        params.put("deptId", deptId);
        String deptName = getBySql(sql,params);
        if (StringUtils.isEmpty(deptName)) {
            response.setStatus(1);
            response.setMessage("该部门不存在");
            return response;
        }

        sql = "select * from tbl_dict where code = :code order by levelCode asc limit 1";
        params.clear();
        params.put("code","wjxz");
        Dict cfgDict = getBySql(sql, params, Dict.class);
        if (cfgDict == null || StringUtils.isEmpty(cfgDict.getValue())) {
            response.setStatus(1);
            response.setMessage("问卷不存在！");
            return response;
        }
        String wjCode = cfgDict.getValue();
        params.put("code",wjCode);
        Dict rootDict = getBySql(sql, params, Dict.class);
        if (rootDict == null) {
            response.setStatus(1);
            response.setMessage("问卷不存在！");
            return response;
        }

        String startTime = rootDict.getValuex();
        String endTime = rootDict.getValuey();
        String curTime = DateUtil.format(new Date(),DateUtil.YYYY_MM_DD_HH_MM_SS);

        if (curTime.compareTo(startTime)<0 || curTime.compareTo(endTime)>0) {
            response.setStatus(1);
            response.setMessage("不在提交时间范围！");
            return response;
        }

        String token = UUID.randomUUID().toString().replace("-", "");
        Calendar calendar = Calendar.getInstance();
        calendar.add(Calendar.DATE, 30);
        String tokeLimit = DateUtil.format(calendar.getTime(), DateUtil.YYYY_MM_DD_HH_MM_SS);

        sql = "select su.*  from app_user su  where su.mobile = :mobile ";
        params.clear();
        params.put("mobile", mobile);

        AppUser user = getBySql(sql, params, AppUser.class);
        if (user == null) {
            user = new AppUser();
            user.setDefaultTime();
            user.setMobile(mobile);
            user.setDeptName(deptName);
            user.setDeptId(deptId);
            user.setToken(token);
            user.setTokenLimit(tokeLimit);
            save(user);
        }else {
            sql = "select * from app_wj_result where wjCode = :code and userId = :userId";
            params.clear();
            params.put("code",wjCode);
            params.put("userId",user.getId());
            AppWjResult oldWjResult = getBySql(sql, params, AppWjResult.class);
            if (oldWjResult != null) {
                response.setStatus(1);
                response.setMessage("您已参与过本次问卷！");
                return response;
            }
            user.setToken(token);
            user.setTokenLimit(tokeLimit);

            if (!deptId.equals(user.getDeptId())) {
                user.setDeptName(deptName);
                user.setDeptId(deptId);
            }

            update(user);
        }

        Map<String, Object> data = new HashMap<>();
        data.put("token", token);
        data.put("mobile", mobile);
        data.put("type", user.getType());

        response.setData(data);
        return response;
    }

    @Override
    public BaseResponse checkAppUserMobile240925(String mobile,String deptName) throws Exception {
        BaseResponse response = new BaseResponse();

        String sql = "select su.*  from app_user su  where su.mobile = :mobile ";
        Map<String, Object> params = new HashMap<>();
        params.put("mobile", mobile);

        AppUser user = getBySql(sql, params, AppUser.class);
        if (user == null) {
            response.setStatus(1);
            response.setMessage("该用户不存在");
            return response;
        }

        if (!deptName.equals(user.getDeptName())) {
            response.setStatus(1);
            response.setMessage("部门不匹配");
            return response;
        }

        sql = "select * from tbl_dict where code = :code order by levelCode asc limit 1";
        params.clear();
        params.put("code","wjxz");
        Dict cfgDict = getBySql(sql, params, Dict.class);
        if (cfgDict == null || StringUtils.isEmpty(cfgDict.getValue())) {
            response.setStatus(1);
            response.setMessage("问卷不存在！");
            return response;
        }
        String wjCode = cfgDict.getValue();
        params.put("code",wjCode);
        Dict rootDict = getBySql(sql, params, Dict.class);
        if (rootDict == null) {
            response.setStatus(1);
            response.setMessage("问卷不存在！");
            return response;
        }

        String startTime = rootDict.getValuex();
        String endTime = rootDict.getValuey();
        String curTime = DateUtil.format(new Date(),DateUtil.YYYY_MM_DD_HH_MM_SS);

        if (curTime.compareTo(startTime)<0 || curTime.compareTo(endTime)>0) {
            response.setStatus(1);
            response.setMessage("不在提交时间范围！");
            return response;
        }

        sql = "select * from app_wj_result where wjCode = :code and userId = :userId";
        params.clear();
        params.put("code",wjCode);
        params.put("userId",user.getId());
        AppWjResult oldWjResult = getBySql(sql, params, AppWjResult.class);
        if (oldWjResult != null) {
            response.setStatus(1);
            response.setMessage("您已参与过本次问卷！");
            return response;
        }
        String token = UUID.randomUUID().toString().replace("-", "");
        Calendar calendar = Calendar.getInstance();
        calendar.add(Calendar.DATE, 30);
        String tokeLimit = DateUtil.format(calendar.getTime(), DateUtil.YYYY_MM_DD_HH_MM_SS);
        user.setToken(token);
        user.setTokenLimit(tokeLimit);

        update(user);

        Map<String, Object> data = new HashMap<>();
        data.put("token", token);
        data.put("mobile", mobile);
        data.put("type", user.getType());

        response.setData(data);
        return response;
    }

    @Override
    public BaseResponse loginByCode(String mobile, String code) throws Exception {
        BaseResponse response = new BaseResponse();

        String sql = "select * from tbl_sms_code where mobile = :mobile and  temp_code= :code and temp_code_limit>=now() ";
        Map<String, Object> params = new HashMap<>();
        params.put("mobile", mobile);
        params.put("code", code);

        SmsCode smscode = getBySql(sql, params, SmsCode.class);
        if (smscode == null) {

            response.setStatus(1);
            response.setMessage("验证码错误！");
            return response;
        }

        sql = "select su.*  from jy_user su  where su.mobile = :mobile ";
        params.clear();
        params.put("mobile", mobile);
        AppUser user = getBySql(sql, params, AppUser.class);
        if (user == null) {
            response.setStatus(1);
            response.setMessage("该用户不存在");
            return response;
        }
        String token = UUID.randomUUID().toString().replace("-", "");
        Calendar calendar = Calendar.getInstance();
        calendar.add(Calendar.DATE, 30);
        String tokeLimit = DateUtil.format(calendar.getTime(), DateUtil.YYYY_MM_DD_HH_MM_SS);
        user.setToken(token);
        user.setTokenLimit(tokeLimit);

        update(user);

        return response;
    }
    @Override
    public BaseResponse checkCode(String mobile, String code) throws Exception {
        BaseResponse response = new BaseResponse();

        String sql = "select * from tbl_sms_code where mobile = :mobile and  temp_code= :code and temp_code_limit>=now() ";
        Map<String, Object> params = new HashMap<>();
        params.put("mobile", mobile);
        params.put("code", code);

        SmsCode smscode = getBySql(sql, params, SmsCode.class);
        if (smscode == null) {

            response.setStatus(1);
            response.setMessage("验证码错误！");
            return response;
        }
        response.setStatus(0);
        return response;
    }


    @Override
    public BaseResponse addNotice(Notice notice, MultipartFile pic, MultipartFile video, HttpServletRequest request) throws Exception {
        Integer id = notice.getId();
        if (id == null) {
            notice.setCreatetime(DateUtil.format(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
            notice.setStatus(0);
            save(notice);
        } else {
            Notice oldNotice = get(Notice.class, id);
            BeanUtils.copyProperties(notice, oldNotice, "createtime");
            notice = oldNotice;
        }

        if (pic != null) {
            String basePath = request.getSession().getServletContext().getRealPath("/");
            String dirPath = PropertiesUtil.getValue("imgPath");
            File fileDir = new File(basePath + dirPath);
            if (!fileDir.exists()) {
                fileDir.mkdirs();
            }
            SysFile sysFile = uploadFile(pic, basePath, dirPath, notice.getId().toString());

            String org_license = notice.getPic();
            if (StringUtils.isNotEmpty(org_license)) {
                File file = new File(basePath, org_license);
                try {
                    file.delete();
                } catch (Exception ex) {
                    ex.printStackTrace();
                }

            }
            notice.setPic(sysFile.getFilePath());
        }

        if (video != null) {
            String basePath = request.getSession().getServletContext().getRealPath("/");
            String dirPath = PropertiesUtil.getValue("imgPath");
            File fileDir = new File(basePath + dirPath);
            if (!fileDir.exists()) {
                fileDir.mkdirs();
            }
            SysFile sysFile = uploadFile(video, basePath, dirPath, notice.getId().toString());

            String org_license = notice.getPic();
            if (StringUtils.isNotEmpty(org_license)) {
                File file = new File(basePath, org_license);
                try {
                    file.delete();
                } catch (Exception ex) {
                    ex.printStackTrace();
                }

            }
            notice.setVideo(sysFile.getFilePath());
        }

        update(notice);
        BaseResponse response = new BaseResponse();
        response.setStatus(0);
        return response;
    }

    @Override
    public BaseResponse getNotice(Integer id) throws Exception {
        BaseResponse response = new BaseResponse();
        String sql = " select * from app_notice where id = :id ";
        Map<String, Object> params = new HashMap<>();
        params.put("id", id);
        Map<String, Object> data = getMapBySql(sql, params);
        response.setStatus(0);
        response.setData(data);
        return response;
    }

    @Override
    public BaseResponse deleteNotice(Integer id) throws Exception {
        BaseResponse response = new BaseResponse();
        String sql = " delete from app_notice where id = :id ";
        Map<String, Object> params = new HashMap<>();
        params.put("id", id);
        executeSql(sql, params);
        response.setStatus(0);
        return response;
    }

    @Override
    public BaseResponse getNoticeList(String type, String status, String keyword, String startTime, String endTime, Integer pageNum, Integer pageSize) throws Exception {

        String sql = "select * from app_notice n where";
        String cntsql = "select count(*) from app_notice n where";

        String orderBy = " order by  n.createtime desc";
        StringBuilder whereClause = new StringBuilder();
        Map<String, Object> params = new HashMap<>();

        if (StringUtils.isNotEmpty(type)) {
            whereClause.append("and  n.type = :type").append(" ");
            params.put("type", type);
        }

        if (StringUtils.isNotEmpty(status)) {
            whereClause.append("and  n.status = :status").append(" ");
            params.put("status", status);
        }
        if (StringUtils.isNotEmpty(keyword)) {
            whereClause.append("and  (n.title like :keyword or n.content like :keyword ) ").append(" ");
            params.put("keyword", "%" + keyword + "%");
        }

        if (StringUtils.isNotEmpty(startTime)) {
            whereClause.append("and  n.createtime >= :startTime").append(" ");
            params.put("startTime", startTime);
        }
        if (StringUtils.isNotEmpty(endTime)) {
            whereClause.append("and n.createtime <= :endTime").append(" ");
            params.put("endTime", endTime);
        }
        if (whereClause.length() > 0) {
            sql += whereClause.substring(3, whereClause.length());
            cntsql += whereClause.substring(3, whereClause.length());
        } else {
            sql = sql.replace("where", "");
            sql = sql.replace("WHERE", "");

            cntsql = cntsql.replace("where", "");
            cntsql = cntsql.replace("WHERE", "");
        }
        sql += orderBy;
        Long cnt = countBySql(cntsql, params);
        List<Map<String, Object>> list = null;
        if (cnt > 0) {
            if (pageSize != null && pageSize > 0 && pageNum != null && pageNum > 0) {
                list = findMapBySql(sql, params, pageNum, pageSize);
            } else {
                list = findMapBySql(sql, params);
            }
        }

        Map<String, Object> data = new HashMap<>();
        data.put("count", cnt);
        data.put("list", list);

        BaseResponse response = new BaseResponse();
        response.setStatus(0);
        response.setData(data);
        return response;
    }

    public SysFile uploadFile(MultipartFile file, String base, String dir, String formId) throws Exception {
        String filePrefixFormat = "yyyyMMddHHmmssS";
        String originalFilename = file.getOriginalFilename();
        int index = originalFilename.lastIndexOf(".");
        String uuid = UUID.randomUUID().toString().replace("-", "");
        String savedName = DateUtil.format(new Date(), filePrefixFormat) + "_" + uuid + originalFilename.substring(index);
        String path = base + dir + savedName;

        File filepath = new File(path);
        if (!filepath.getParentFile().exists()) {
            filepath.getParentFile().mkdirs();
        }
        //将上传文件保存到一个目标文件当中
        File realFile = new File(path);
        file.transferTo(realFile);

        /*File scleFile = null;
        if (realFile.length() > 100 * 1024) {
            scleFile = new File(base + dir + "scale_" + realFile.getName());
            ImageUtils.reduceImg(realFile, scleFile);
            realFile.delete();
        } else {
            scleFile = realFile;
        }
        savedName = scleFile.getName();*/

        SysFile sysFile = new SysFile();
        sysFile.setFileName(originalFilename);
        sysFile.setSavedName(savedName);
        sysFile.setCreateDateTime(new Date());
        sysFile.setUpdateDateTime(new Date());
        sysFile.setFilePath(dir + savedName);
        sysFile.setFormId(formId);
        sysFile.setDeleted(0);
        sysFile.setFileSize(realFile.length());
        return sysFile;
    }


    @Override
    public BaseResponse addNews(News news, MultipartFile pic, MultipartFile video, HttpServletRequest request) throws Exception {
        Integer id = news.getId();
        if (id == null) {
            news.setCreatetime(DateUtil.format(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
            news.setStatus(0);
            save(news);
        } else {
            News oldNews = get(News.class, id);
            BeanUtils.copyProperties(news, oldNews, "createtime");
            news = oldNews;
        }

        if (pic != null) {
            String basePath = request.getSession().getServletContext().getRealPath("/");
            String dirPath = PropertiesUtil.getValue("imgPath");
            File fileDir = new File(basePath + dirPath);
            if (!fileDir.exists()) {
                fileDir.mkdirs();
            }
            SysFile sysFile = uploadFile(pic, basePath, dirPath, news.getId().toString());

            String org_license = news.getPic();
            if (StringUtils.isNotEmpty(org_license)) {
                File file = new File(basePath, org_license);
                try {
                    file.delete();
                } catch (Exception ex) {
                    ex.printStackTrace();
                }

            }
            news.setPic(sysFile.getFilePath());
        }

        if (video != null) {
            String basePath = request.getSession().getServletContext().getRealPath("/");
            String dirPath = PropertiesUtil.getValue("imgPath");
            File fileDir = new File(basePath + dirPath);
            if (!fileDir.exists()) {
                fileDir.mkdirs();
            }
            SysFile sysFile = uploadFile(video, basePath, dirPath, news.getId().toString());

            String org_license = news.getPic();
            if (StringUtils.isNotEmpty(org_license)) {
                File file = new File(basePath, org_license);
                try {
                    file.delete();
                } catch (Exception ex) {
                    ex.printStackTrace();
                }

            }
            news.setVideo(sysFile.getFilePath());
        }

        update(news);

        BaseResponse response = new BaseResponse();
        response.setStatus(0);
        return response;
    }

    @Override
    public BaseResponse getNews(Integer id) throws Exception {
        BaseResponse response = new BaseResponse();
        String sql = " select * from app_news where id = :id ";
        Map<String, Object> params = new HashMap<>();
        params.put("id", id);
        Map<String, Object> data = getMapBySql(sql, params);
        response.setStatus(0);
        response.setData(data);
        return response;
    }

    @Override
    public BaseResponse deleteNews(Integer id) throws Exception {
        BaseResponse response = new BaseResponse();
        String sql = " delete from app_news where id = :id ";
        Map<String, Object> params = new HashMap<>();
        params.put("id", id);
        executeSql(sql, params);
        response.setStatus(0);
        return response;
    }

    @Override
    public BaseResponse getNewsList(String type, String isshow, String status, String keyword, String startTime, String endTime, Integer pageNum, Integer pageSize) throws Exception {

        String sql = "select * from app_news n where";
        String cntsql = "select count(*) from app_news n where";

        String orderBy = " order by  n.createtime desc";
        StringBuilder whereClause = new StringBuilder();
        Map<String, Object> params = new HashMap<>();

        if (StringUtils.isNotEmpty(type)) {
            whereClause.append("and  n.type = :type").append(" ");
            params.put("type", type);
        }

        if (StringUtils.isNotEmpty(isshow)) {
            whereClause.append("and  n.isshow = :isshow").append(" ");
            params.put("isshow", isshow);
        }

        if (StringUtils.isNotEmpty(status)) {
            whereClause.append("and  n.status = :status").append(" ");
            params.put("status", status);
        }
        if (StringUtils.isNotEmpty(keyword)) {
            whereClause.append("and  (n.title like :keyword or n.content like :keyword ) ").append(" ");
            params.put("keyword", "%" + keyword + "%");
        }

        if (StringUtils.isNotEmpty(startTime)) {
            whereClause.append("and  n.createtime >= :startTime").append(" ");
            params.put("startTime", startTime);
        }
        if (StringUtils.isNotEmpty(endTime)) {
            whereClause.append("and n.createtime <= :endTime").append(" ");
            params.put("endTime", endTime);
        }
        if (whereClause.length() > 0) {
            sql += whereClause.substring(3, whereClause.length());
            cntsql += whereClause.substring(3, whereClause.length());
        } else {
            sql = sql.replace("where", "");
            sql = sql.replace("WHERE", "");

            cntsql = cntsql.replace("where", "");
            cntsql = cntsql.replace("WHERE", "");
        }
        sql += orderBy;
        Long cnt = countBySql(cntsql, params);
        List<Map<String, Object>> list = null;
        if (cnt > 0) {
            if (pageSize != null && pageSize > 0 && pageNum != null && pageNum > 0) {
                list = findMapBySql(sql, params, pageNum, pageSize);
            } else {
                list = findMapBySql(sql, params);
            }
        }

        Map<String, Object> data = new HashMap<>();
        data.put("count", cnt);
        data.put("list", list);

        BaseResponse response = new BaseResponse();
        response.setStatus(0);
        response.setData(data);
        return response;
    }


    @Override
    public BaseResponse getArea(String code) throws Exception {
        BaseResponse response = new BaseResponse();
        String sql = "select * from tbl_area where ";
        Map<String, Object> params = new HashMap<>();
        if (StringUtils.isNotEmpty(code)) {
            String endZero = getEndZero(code);
            int length = endZero.length();
            endZero = length % 2 == 0 ? endZero : endZero.substring(0, length - 1);
            if (StringUtils.isEmpty(endZero)) {
                response.setStatus(1);
                response.setMessage("无下一级！");
                return response;
            } else {
                int index = code.lastIndexOf(endZero);
                String prefix = code.substring(0, index);

                sql += " code like :prefix and code not like :endZero ";
                params.put("prefix", prefix + "%");
                params.put("endZero", "%" + endZero);
                if (endZero.length() > 2) {
                    String nextEnd = endZero.substring(0, endZero.length() - 2);
                    sql += " and code like :nextEnd ";
                    params.put("nextEnd", "%" + nextEnd);
                }
            }

        } else {
            String selectSql = " select length(code) from tbl_area limit 1 ";
            Long cnt = countBySql(selectSql);
            String endZero = "";
            for (long i = 0; i < cnt - 2; i++) {
                endZero += "0";
            }
            if (endZero.length() > 2) {
                sql += " code like :endZero ";
                params.put("endZero", "%" + endZero);
            }

        }
        sql += " order by code asc";
        List<Map<String, Object>> areas = findMapBySql(sql, params);
        response.setStatus(0);
        response.setData(areas);

        return response;
    }

    @Override
    public BaseResponse getAreaTree() throws Exception {
        BaseResponse response = new BaseResponse();
        String sql = "select * from tbl_area order by code asc ";
        Map<String, Object> params = new HashMap<>();
        List<Map<String, Object>> areas = findMapBySql(sql, params);
        List<TreeNode> tnlist = null;

        Map<String, TreeNode> nodelist = new LinkedHashMap<String, TreeNode>();


        for (Map<String, Object> area : areas) {
            String code = area.get("code").toString();
            String name = area.get("name").toString();
            String endZero = getEndZero(code);
            endZero = endZero.length() % 2 == 0 ? endZero : endZero.substring(0, endZero.length() - 1);
            System.out.println("endZero:" + endZero);
            String parentId = endZero.length() == code.length() - 2 ? null : code.substring(0, code.length() - endZero.length() - 2) + "00" + endZero;

            System.out.println(code + "/" + parentId);
            TreeNode node = new TreeNode();
            node.setText(name);
            node.setId(code);
            node.setParentId(parentId);
            node.setLevelCode(code);
            nodelist.put(node.getId(), node);
        }
        tnlist = TreeUtil.getNodeList(nodelist);
        response.setStatus(0);
        response.setData(tnlist);

        return response;
    }

    private String getEndZero(String code) {
        String temp = code;
        String end = "";
        while (temp.endsWith("0") && StringUtils.isNotEmpty(temp)) {
            temp = temp.substring(0, temp.length() - 1);
            end += "0";
        }
        return end;
    }




    @Override
    public BaseResponse editAppNote(AppNote note) throws Exception {
        String id = note.getId();
        if (StringUtils.isEmpty(id)) {
            note.setDefaultTime();
            save(note);
        } else {
            AppNote oldAppNote = get(AppNote.class, id);
            BeanUtils.copyProperties(note, oldAppNote, "createtime");
            note = oldAppNote;
            note.setDefaultUpdateTime();
            update(note);
        }
        BaseResponse response = new BaseResponse();
        response.setStatus(0);
        return response;
    }

    @Override
    public BaseResponse getAppNote(String id) throws Exception {
        BaseResponse response = new BaseResponse();
        String sql = " select n.*,u.name userName,u.mobile userMobile from app_note n left join tbl_user u on u.id = n.userId " +
                "where n.id = :id ";
        Map<String, Object> params = new HashMap<>();
        params.put("id", id);
        Map<String, Object> data = getMapBySql(sql, params);
        if (data == null) {
            response.setStatus(1);
            response.setMessage("对象不存在！");
            return response;
        }
        response.setStatus(0);
        response.setData(data);
        return response;
    }

    @Override
    public BaseResponse deleteAppNote(String id) throws Exception {
        BaseResponse response = new BaseResponse();
        String sql = " delete from app_note where id = :id ";
        Map<String, Object> params = new HashMap<>();
        params.put("id", id);
        executeSql(sql, params);
        response.setStatus(0);
        return response;
    }

    @Override
    public BaseResponse getAppNoteList(String userId, String deptId, String keyword, String startTime, String endTime, Integer pageNum, Integer pageSize) throws Exception {
        BaseResponse response = new BaseResponse();
        String sql = "select count(*) from app_note n left join tbl_user u on u.id = n.userId  where";
        StringBuilder whereClause = new StringBuilder();
        Map<String, Object> params = new HashMap<>();

        if (StringUtils.isNotEmpty(userId)) {
            whereClause.append("and  n.userId = :userId").append(" ");
            params.put("userId", userId);
        }

        if (StringUtils.isNotEmpty(deptId)) {
            whereClause.append("and  n.deptId = :deptId").append(" ");
            params.put("deptId", deptId);
        }
        if (StringUtils.isNotEmpty(keyword)) {
            whereClause.append("and  (n.noteTitle like :keyword or n.noteContent like :keyword or u.name like :keyword or u.mobile like :keyword)").append(" ");
            params.put("keyword", "%" + keyword + "%");

        }

        if (StringUtils.isNotEmpty(startTime)) {
            whereClause.append("and  concat(n.noteDate,' ',n.noteTime) >= :startTime").append(" ");
            params.put("startTime", startTime);
        }
        if (StringUtils.isNotEmpty(endTime)) {
            whereClause.append("and concat(n.noteDate,' ',n.noteTime) <= :endTime").append(" ");
            params.put("endTime", endTime);
        }

        if (whereClause.length() > 0) {
            sql += whereClause.substring(3, whereClause.length());
        } else {
            sql = sql.replace("where", "");
            sql = sql.replace("WHERE", "");
        }

        Long cnt = countBySql(sql, params);
        List<Map<String, Object>> list = cnt > 0 ? getAppNoteDataList(userId, deptId, keyword, startTime, endTime, pageNum, pageSize) : null;
        Map<String, Object> data = new HashMap<>();
        data.put("count", cnt);
        data.put("list", list);
        response.setStatus(0);
        response.setData(data);
        return response;
    }

    @Override
    public List<Map<String, Object>> getAppNoteDataList(String userId, String deptId, String keyword, String startTime, String endTime, Integer pageNum, Integer pageSize) throws Exception {

        String sql = " select n.*,u.name userName,u.mobile userMobile from app_note n left join tbl_user u on u.id = n.userId " +
                "where";
        StringBuilder whereClause = new StringBuilder();
        Map<String, Object> params = new HashMap<>();
        if (StringUtils.isNotEmpty(userId)) {
            whereClause.append("and  n.userId = :userId").append(" ");
            params.put("userId", userId);
        }

        if (StringUtils.isNotEmpty(deptId)) {
            whereClause.append("and  n.deptId = :deptId").append(" ");
            params.put("deptId", deptId);
        }
        if (StringUtils.isNotEmpty(keyword)) {
            whereClause.append("and  (n.noteTitle like :keyword or n.noteContent like :keyword or u.name like :keyword or u.mobile like :keyword)").append(" ");
            params.put("keyword", "%" + keyword + "%");

        }

        if (StringUtils.isNotEmpty(startTime)) {
            whereClause.append("and  concat(n.noteDate,' ',n.noteTime) >= :startTime").append(" ");
            params.put("startTime", startTime);
        }
        if (StringUtils.isNotEmpty(endTime)) {
            whereClause.append("and concat(n.noteDate,' ',n.noteTime) <= :endTime").append(" ");
            params.put("endTime", endTime);
        }

        if (whereClause.length() > 0) {
            sql += whereClause.substring(3, whereClause.length());
        } else {
            sql = sql.replace("where", "");
            sql = sql.replace("WHERE", "");
        }

        sql += " order by  n.noteDate desc,n.noteTime desc";
        List<Map<String, Object>> list = null;
        if (pageSize != null && pageSize > 0 && pageNum != null && pageNum > 0) {
            list = findMapBySql(sql, params, pageNum, pageSize);
        } else {
            list = findMapBySql(sql, params);
        }
        return list;
    }

    @Override
    public BaseResponse getAppNoteCalender(String userId, String deptId, String month) throws Exception {
        BaseResponse response = new BaseResponse();
        String startTime = month + "-01";
        String endTime = month + "-32";
        String sql = "select max(c_date) from tb_date where c_date<=:startTime and c_weekday = 1";
        Map<String, Object> params = new HashMap<>();
        params.put("startTime", startTime);
        String minDate = getBySql(sql, params);

        sql = "select d.c_date,d.c_weekday,ifnull(n.cnt,0)cnt from tb_date d left join " +
                " (select noteDate,count(*)cnt from app_note where userId=:userId and noteDate>=:startTime " +
                "  and noteDate<:endTime group by noteDate) n on d.c_date = n.noteDate " +
                " where c_date>=:startTime and c_date<:endTime order by c_date asc ";
        params.put("userId", userId);
        params.put("startTime", minDate);
        params.put("endTime", endTime);
        List<Map<String, Object>> list = findMapBySql(sql, params);
        response.setData(list);

        return response;
    }



    @Override
    public BaseResponse editHelp(AppHelp help) throws Exception {
        BaseResponse response = new BaseResponse();

        String id = help.getId();
        if (StringUtils.isEmpty(id)) {
            help.setDefaultTime();
            save(help);
            id = help.getId();
        } else {
            AppHelp oldHelp = get(AppHelp.class, id);

            BeanUtils.copyProperties(help, oldHelp, "createtime", "updatetime");
            oldHelp.setDefaultUpdateTime();

            help = oldHelp;
            update(help);

        }
        return response;
    }

    @Override
    public BaseResponse getHelpList(String type, String keyword, String status, String startTime, String endTime, Integer pageNum, Integer pageSize) throws Exception {
        BaseResponse response = new BaseResponse();
        String cntsql = " select count(*) from app_help rco " +
                "  where";
        String sql = " select id,createtime,title,status,type from app_help rco where";

        StringBuilder whereClause = new StringBuilder();
        Map<String, Object> params = new HashMap<>();


        if (StringUtils.isNotEmpty(keyword)) {
            whereClause.append("and  (rco.title like :keyword)").append(" ");
            params.put("keyword", "%" + keyword + "%");

        }

        if (StringUtils.isNotEmpty(type)) {
            whereClause.append("and  rco.type = :type").append(" ");
            params.put("type", type);
        }

        if (StringUtils.isNotEmpty(status)) {
            whereClause.append("and  rco.status = :status").append(" ");
            params.put("status", status);
        }

        if (StringUtils.isNotEmpty(startTime)) {

            whereClause.append("and  rco.createtime >= :startTime").append(" ");
            params.put("startTime", startTime);
        }
        if (StringUtils.isNotEmpty(endTime)) {
            if (endTime.length() == 10) {
                endTime += " 23:59:59";
            }
            whereClause.append("and rco.createtime <= :endTime").append(" ");
            params.put("endTime", endTime);
        }
        if (whereClause.length() > 0) {
            cntsql += whereClause.substring(3, whereClause.length());
            sql += whereClause.substring(3, whereClause.length());
        } else {
            cntsql = cntsql.replace("where", "");
            sql = sql.replace("where", "");
        }
        sql += " order by rco.createtime desc";
        Long cnt = countBySql(cntsql, params);
        List<Map<String, Object>> dataList = null;
        if (cnt > 0) {
            if (pageSize != null && pageSize > 0 && pageNum != null && pageNum > 0) {
                dataList = findMapBySql(sql, params, pageNum, pageSize);
            } else {
                dataList = findMapBySql(sql, params);
            }
        }
        Map<String, Object> data = new HashMap<>();
        data.put("count", cnt);
        data.put("list", dataList);
        response.setData(data);
        return response;
    }

    @Override
    public BaseResponse getHelp(String id) throws Exception {
        BaseResponse response = new BaseResponse();
        String sql = " select rco.* from app_help rco where id=:id";
        Map<String, Object> params = new HashMap<>();
        params.put("id", id);

        Map<String, Object> data = getMapBySql(sql, params);
        if (data == null) {

            response.setStatus(1);
            response.setMessage("数据不存在！");
            return response;

        }
        response.setData(data);
        return response;
    }


    @Override
    public BaseResponse getItemChangeRecordListByAuth(String userId, String type, String keyword, String startTime, String endTime, Integer pageNum, Integer pageSize) throws Exception {
        BaseResponse response = new BaseResponse();
        String sql = "select count(*) from item_change_record ic " +
                " left join tbl_user u on u.id = ic.userid " +
                " where";
        StringBuilder whereClause = new StringBuilder();
        Map<String, Object> params = new HashMap<>();

        List<String> userDataScope = getUserDataScope(userId);
        if (userDataScope == null || userDataScope.size() == 0) {
            whereClause.append("and  ic.userid = :userId").append(" ");
            params.put("userId", userId);
        } else {
            whereClause.append("and  u.dept_id in (:deptIds)").append(" ");
            params.put("deptIds", userDataScope);
        }


        if (StringUtils.isNotEmpty(keyword)) {
            whereClause.append("and  (u.name like :keyword or u.mobile like :keyword or ic.name like :keyword)").append(" ");
            params.put("keyword", "%" + keyword + "%");

        }

        if (StringUtils.isNotEmpty(type)) {
            whereClause.append("and ic.type = :type").append(" ");
            params.put("type", type);
        }

        if (StringUtils.isNotEmpty(startTime)) {
            whereClause.append("and ic.createtime >= :startTime").append(" ");
            params.put("startTime", startTime);
        }
        if (StringUtils.isNotEmpty(endTime)) {
            whereClause.append("and ic.createtime <= :endTime").append(" ");
            params.put("endTime", endTime);
        }

        if (whereClause.length() > 0) {
            sql += whereClause.substring(3, whereClause.length());
        } else {
            sql = sql.replace("where", "");
            sql = sql.replace("WHERE", "");
        }

        Long cnt = countBySql(sql, params);
        List<Map<String, Object>> list = cnt > 0 ? getItemChangeRecordsByAuth(userId, userDataScope, type, keyword, startTime, endTime, pageNum, pageSize) : null;
        Map<String, Object> data = new HashMap<>();
        data.put("count", cnt);
        data.put("list", list);
        response.setStatus(0);
        response.setData(data);
        return response;
    }

    @Override
    public List<Map<String, Object>> getItemChangeRecordsByAuth(String userId, List<String> userDataScope, String type, String keyword, String startTime, String endTime, Integer pageNum, Integer pageSize) throws Exception {
        String sql = " select ic.*,u.name userName,u.mobile userMobile,d.name typeText " +
                " from item_change_record ic " +
                " left join tbl_dict d on d.code = ic.type " +
                " left join tbl_user u on u.id = ic.userid " +
                " where";
        StringBuilder whereClause = new StringBuilder();
        Map<String, Object> params = new HashMap<>();

        if (userDataScope != null) {
            if (userDataScope.size() == 0) {
                whereClause.append("and ic.userid = :userId").append(" ");
                params.put("userId", userId);
            } else {
                whereClause.append("and u.dept_id in (:deptIds)").append(" ");
                params.put("deptIds", userDataScope);
            }

        } else {
            userDataScope = getUserDataScope(userId);
            if (userDataScope == null || userDataScope.size() == 0) {
                whereClause.append("and ic.userid = :userId").append(" ");
                params.put("userId", userId);
            } else {
                whereClause.append("and u.dept_id in (:deptIds)").append(" ");
                params.put("deptIds", userDataScope);
            }
        }

        if (StringUtils.isNotEmpty(keyword)) {
            whereClause.append("and  (u.name like :keyword or u.mobile like :keyword or ic.name like :keyword)").append(" ");
            params.put("keyword", "%" + keyword + "%");
        }

        if (StringUtils.isNotEmpty(type)) {
            whereClause.append("and ic.type = :type").append(" ");
            params.put("type", type);
        }

        if (StringUtils.isNotEmpty(startTime)) {
            whereClause.append("and ic.createtime >= :startTime").append(" ");
            params.put("startTime", startTime);
        }
        if (StringUtils.isNotEmpty(endTime)) {
            whereClause.append("and ic.createtime <= :endTime").append(" ");
            params.put("endTime", endTime);
        }

        if (whereClause.length() > 0) {
            sql += whereClause.substring(3, whereClause.length());
        } else {
            sql = sql.replace("where", "");
            sql = sql.replace("WHERE", "");
        }

        sql += " order by  ic.createtime desc";
        List<Map<String, Object>> list = null;
        if (pageSize != null && pageSize > 0 && pageNum != null && pageNum > 0) {
            list = findMapBySql(sql, params, pageNum, pageSize);
        } else {
            list = findMapBySql(sql, params);
        }
        return list;
    }


    @Override
    public BaseResponse getItemChangeRecordList(String empId, String type, String keyword, String startTime, String endTime, Integer pageNum, Integer pageSize) throws Exception {
        BaseResponse response = new BaseResponse();
        String sql = "select count(*) from item_change_record ic " +
                " left join tbl_user u on u.id = ic.userid " +
                " where";
        StringBuilder whereClause = new StringBuilder();
        Map<String, Object> params = new HashMap<>();

        if (StringUtils.isNotEmpty(keyword)) {
            whereClause.append("and  (u.name like :keyword or u.mobile like :keyword or ic.name like :keyword)").append(" ");
            params.put("keyword", "%" + keyword + "%");

        }

        if (StringUtils.isNotEmpty(type)) {
            whereClause.append("and ic.type = :type").append(" ");
            params.put("type", type);
        }

        if (StringUtils.isNotEmpty(startTime)) {
            whereClause.append("and ic.createtime >= :startTime").append(" ");
            params.put("startTime", startTime);
        }
        if (StringUtils.isNotEmpty(endTime)) {
            whereClause.append("and ic.createtime <= :endTime").append(" ");
            params.put("endTime", endTime);
        }

        if (whereClause.length() > 0) {
            sql += whereClause.substring(3, whereClause.length());
        } else {
            sql = sql.replace("where", "");
            sql = sql.replace("WHERE", "");
        }

        Long cnt = countBySql(sql, params);
        List<Map<String, Object>> list = cnt > 0 ? getItemChangeRecords(empId, type, keyword, startTime, endTime, pageNum, pageSize) : null;
        Map<String, Object> data = new HashMap<>();
        data.put("count", cnt);
        data.put("list", list);
        response.setStatus(0);
        response.setData(data);
        return response;
    }

    @Override
    public List<Map<String, Object>> getItemChangeRecords(String empId, String type, String keyword, String startTime, String endTime, Integer pageNum, Integer pageSize) throws Exception {
        String sql = " select ic.*,u.name userName,u.mobile userMobile,d.name typeText " +
                " from item_change_record ic " +
                " left join tbl_dict d on d.code = ic.type " +
                " left join tbl_user u on u.id = ic.userid " +
                " where";
        StringBuilder whereClause = new StringBuilder();
        Map<String, Object> params = new HashMap<>();


        if (StringUtils.isNotEmpty(keyword)) {
            whereClause.append("and  (u.name like :keyword or u.mobile like :keyword or ic.name like :keyword)").append(" ");
            params.put("keyword", "%" + keyword + "%");
        }

        if (StringUtils.isNotEmpty(type)) {
            whereClause.append("and ic.type = :type").append(" ");
            params.put("type", type);
        }

        if (StringUtils.isNotEmpty(startTime)) {
            whereClause.append("and ic.createtime >= :startTime").append(" ");
            params.put("startTime", startTime);
        }
        if (StringUtils.isNotEmpty(endTime)) {
            whereClause.append("and ic.createtime <= :endTime").append(" ");
            params.put("endTime", endTime);
        }

        if (whereClause.length() > 0) {
            sql += whereClause.substring(3, whereClause.length());
        } else {
            sql = sql.replace("where", "");
            sql = sql.replace("WHERE", "");
        }

        sql += " order by  ic.createtime desc";
        List<Map<String, Object>> list = null;
        if (pageSize != null && pageSize > 0 && pageNum != null && pageNum > 0) {
            list = findMapBySql(sql, params, pageNum, pageSize);
        } else {
            list = findMapBySql(sql, params);
        }
        return list;
    }

    @Override
    public BaseResponse getItemChangeRecord(String id) throws Exception {
        BaseResponse response = new BaseResponse();
        String sql = " select ic.*,u.name userName,u.mobile userMobile,d.name typeText " +
                " from item_change_record ic " +
                " left join tbl_dict d on d.code = ic.type " +
                " left join tbl_user u on u.id = ic.userid " +
                " where ic.id=:id";
        Map<String, Object> params = new HashMap<>();
        params.put("id", id);

        Map<String, Object> data = getMapBySql(sql, params);
        if (data == null) {

            response.setStatus(1);
            response.setMessage("数据不存在！");
            return response;

        }
        response.setData(data);
        return response;
    }

    @Override
    public BaseResponse deleteItemChangeRecord(String id) throws Exception {
        BaseResponse response = new BaseResponse();
        String sql = " delete from item_change_record where id = :id ";
        Map<String, Object> params = new HashMap<>();
        params.put("id", id);
        executeSql(sql, params);
        response.setStatus(0);
        return response;
    }

    @Override
    public BaseResponse deleteItemChangeRecords(List<String> ids) throws Exception {
        BaseResponse response = new BaseResponse();
        String sql = " delete from item_change_record where id in (:ids) ";
        Map<String, Object> params = new HashMap<>();
        params.put("ids", ids);
        executeSql(sql, params);
        response.setStatus(0);
        return response;
    }



    @Override
    public BaseResponse editUserFile(UserFile userFile) throws Exception {
        Integer id = userFile.getId();
        if (id == null) {
            userFile.setDefaultTime();
            save(userFile);
        } else {
            UserFile oldUserFile = get(UserFile.class, id);
            BeanUtils.copyProperties(userFile, oldUserFile, "createtime");
            userFile = oldUserFile;
            userFile.setDefaultUpdateTime();
            update(userFile);
        }

        String sql = "update tbl_user set sh='0',shtime=null,reason=null,update_date_time=now(),changetype='附件变更' where id=:userId";
        Map<String, Object> params = new HashMap<>();
        params.put("userId", id);
        executeSql(sql, params);

        BaseResponse response = new BaseResponse();
        response.setStatus(0);
        return response;
    }

    @Override
    public BaseResponse getUserFile(String id) throws Exception {
        BaseResponse response = new BaseResponse();
        String sql = " select a.* from tbl_user_file a  where a.id = :id ";
        Map<String, Object> params = new HashMap<>();
        params.put("id", id);
        Map<String, Object> data = getMapBySql(sql, params);
        if (data == null) {
            response.setStatus(1);
            response.setMessage("对象不存在！");
            return response;
        }
        response.setStatus(0);
        response.setData(data);
        return response;
    }

    @Override
    public BaseResponse deleteUserFile(String id) throws Exception {
        BaseResponse response = new BaseResponse();
        String sql = " delete from tbl_user_file where id = :id ";
        Map<String, Object> params = new HashMap<>();
        params.put("id", id);
        executeSql(sql, params);
        response.setStatus(0);
        return response;
    }

    @Override
    public BaseResponse getUserFileList(String keyword, String userId, String type, String startTime, String endTime, Integer pageNum, Integer pageSize) throws Exception {
        BaseResponse response = new BaseResponse();
        String sql = "select count(*) from tbl_user_file n left join tbl_user u on u.id = n.userId  where";
        StringBuilder whereClause = new StringBuilder();
        Map<String, Object> params = new HashMap<>();

        if (StringUtils.isNotEmpty(userId)) {
            whereClause.append("and  n.userId = :userId").append(" ");
            params.put("userId", userId);
        }

        if (StringUtils.isNotEmpty(type)) {
            whereClause.append("and  n.type = :type").append(" ");
            params.put("type", type);
        }
        if (StringUtils.isNotEmpty(keyword)) {
            whereClause.append("and  (n.filename like :keyword or u.name like :keyword or u.mobile like :keyword)").append(" ");
            params.put("keyword", "%" + keyword + "%");

        }

        if (StringUtils.isNotEmpty(startTime)) {
            whereClause.append("and  n.createtime >= :startTime").append(" ");
            params.put("startTime", startTime);
        }
        if (StringUtils.isNotEmpty(endTime)) {
            whereClause.append("and n.createtime <= :endTime").append(" ");
            params.put("endTime", endTime);
        }

        if (whereClause.length() > 0) {
            sql += whereClause.substring(3, whereClause.length());
        } else {
            sql = sql.replace("where", "");
            sql = sql.replace("WHERE", "");
        }

        Long cnt = countBySql(sql, params);
        List<Map<String, Object>> list = cnt > 0 ? getUserFileDataList(keyword, userId, type, startTime, endTime, pageNum, pageSize) : null;
        Map<String, Object> data = new HashMap<>();
        data.put("count", cnt);
        data.put("list", list);
        response.setStatus(0);
        response.setData(data);
        return response;
    }

    @Override
    public List<Map<String, Object>> getUserFileDataList(String keyword, String userId, String type, String startTime, String endTime, Integer pageNum, Integer pageSize) throws Exception {
        String sql = " select n.*,u.mobile userMobile from tbl_user_file n left join tbl_user u on u.id = n.userId " +
                "where";
        StringBuilder whereClause = new StringBuilder();
        Map<String, Object> params = new HashMap<>();

        if (StringUtils.isNotEmpty(userId)) {
            whereClause.append("and  n.userId = :userId").append(" ");
            params.put("userId", userId);
        }

        if (StringUtils.isNotEmpty(type)) {
            whereClause.append("and  n.type = :type").append(" ");
            params.put("type", type);
        }
        if (StringUtils.isNotEmpty(keyword)) {
            whereClause.append("and  (n.filename like :keyword or u.name like :keyword or u.mobile like :keyword)").append(" ");
            params.put("keyword", "%" + keyword + "%");

        }

        if (StringUtils.isNotEmpty(startTime)) {
            whereClause.append("and  n.createtime >= :startTime").append(" ");
            params.put("startTime", startTime);
        }
        if (StringUtils.isNotEmpty(endTime)) {
            whereClause.append("and n.createtime <= :endTime").append(" ");
            params.put("endTime", endTime);
        }

        if (whereClause.length() > 0) {
            sql += whereClause.substring(3, whereClause.length());
        } else {
            sql = sql.replace("where", "");
            sql = sql.replace("WHERE", "");
        }

        sql += " order by n.createtime desc";
        List<Map<String, Object>> list = null;
        if (pageSize != null && pageSize > 0 && pageNum != null && pageNum > 0) {
            list = findMapBySql(sql, params, pageNum, pageSize);
        } else {
            list = findMapBySql(sql, params);
        }
        return list;
    }



    @Override
    public BaseResponse addTagChangeRecord(TagChangeRecord tagChangeRecord) throws Exception {
        BaseResponse response = new BaseResponse();
        if (StringUtils.isEmpty(tagChangeRecord.getItemid())) {
            tagChangeRecord.setItemid(null);
        }
        User user = get(User.class, tagChangeRecord.getUserid());
        if (user == null) {
            response.setStatus(1);
            response.setMessage("用户不存在！");
            return response;
        }

        tagChangeRecord.setDefaultTime();
        if ("1c8fceac5e2011ec8aebfa163e2442a1".equals(user.getDeptId())) {

            tagChangeRecord.setState(1);
            tagChangeRecord.setDefaultShTime();
            tagChangeRecord.setShUserId(user.getId());
            tagChangeRecord.setShUserName(user.getName());


            if (tagChangeRecord.getChangeType() == 0) {
                String dataaf = tagChangeRecord.getDataaf();
                UserTag userTag = new Gson().fromJson(dataaf, UserTag.class);
                userTag.setDefaultTime();
                save(userTag);
            } else {
                UserTag userTag = new UserTag();
                userTag.setDefaultTime();
                userTag.setType("5");
                userTag.setTagname("本公司");
                String itemid = tagChangeRecord.getItemid();
                String userName = tagChangeRecord.getItemname();
                String deptId = tagChangeRecord.getToDeptId();
                User newUser = null;
                if (tagChangeRecord.getType() == 10) {
                    newUser = new User();
                    newUser.setName(userName);
                    newUser.setDeptId(deptId);
                    save(newUser);

                } else {
                    newUser = get(User.class, itemid);
                    newUser.setDeptId(deptId);
                    update(newUser);
                }

                userTag.setTagtype(tagChangeRecord.getToPost());
                userTag.setTagstarttime(tagChangeRecord.getChangeTime());
                userTag.setUserId(newUser.getId());
                if (tagChangeRecord.getType() == 10 || tagChangeRecord.getType() == 11 ||
                        tagChangeRecord.getType() == 12 || tagChangeRecord.getType() == 13) {
                    save(userTag);
                }
            }

        }
        save(tagChangeRecord);
        return response;
    }

    @Override
    public BaseResponse getTagChangeRecord(String id) throws Exception {
        BaseResponse response = new BaseResponse();
        String sql = " select ic.*  " +
                " from tag_change_record ic " +
                " where ic.id=:id";
        Map<String, Object> params = new HashMap<>();
        params.put("id", id);

        Map<String, Object> data = getMapBySql(sql, params);
        if (data == null) {

            response.setStatus(1);
            response.setMessage("数据不存在！");
            return response;

        }
        response.setData(data);
        return response;
    }


    @Override
    public BaseResponse deleteTagChangeRecord(String id) throws Exception {
        BaseResponse response = new BaseResponse();
        String sql = " delete from tag_change_record where id = :id ";
        Map<String, Object> params = new HashMap<>();
        params.put("id", id);
        executeSql(sql, params);
        response.setStatus(0);
        return response;
    }

    @Override
    public BaseResponse deleteTagChangeRecords(List<String> ids) throws Exception {
        BaseResponse response = new BaseResponse();
        String sql = " delete from tag_change_record where id in (:ids)";
        Map<String, Object> params = new HashMap<>();
        params.put("ids", ids);
        executeSql(sql, params);
        response.setStatus(0);
        return response;
    }

    @Override
    public BaseResponse getTagChangeRecordList(String userId, String deptId, String changeType, String type, String itemId, String keyword, String state, String shUserId, String startTime, String endTime, Integer pageNum, Integer pageSize) throws Exception {
        BaseResponse response = new BaseResponse();
        String sql = "select count(*) from tag_change_record ic " +
                " left join tbl_user u on u.id = ic.userid " +
                " where";
        StringBuilder whereClause = new StringBuilder();
        Map<String, Object> params = new HashMap<>();

        if (StringUtils.isNotEmpty(userId)) {
            whereClause.append("and ic.userid = :userId").append(" ");
            params.put("userId", userId);
        }

        if (StringUtils.isNotEmpty(keyword)) {
            whereClause.append("and  (ic.itemname like :keyword)").append(" ");
            params.put("keyword", "%" + keyword + "%");
        }

        if (StringUtils.isNotEmpty(changeType)) {
            whereClause.append("and ic.changeType = :changeType").append(" ");
            params.put("changeType", changeType);
        }

        if (StringUtils.isNotEmpty(type)) {
            whereClause.append("and ic.type = :type").append(" ");
            params.put("type", type);
        }

        if (StringUtils.isNotEmpty(itemId)) {
            whereClause.append("and ic.itemid = :itemId").append(" ");
            params.put("itemId", itemId);
        }

        if (StringUtils.isNotEmpty(state)) {
            whereClause.append("and ic.state in (:stateList)").append(" ");
            params.put("stateList", state.split(","));
        }

        if (StringUtils.isNotEmpty(shUserId)) {
            whereClause.append("and  ic.shUserId = :shUserId").append(" ");
            params.put("shUserId", shUserId);
        }

        if (StringUtils.isNotEmpty(startTime)) {
            whereClause.append("and ic.createtime >= :startTime").append(" ");
            params.put("startTime", startTime);
        }
        if (StringUtils.isNotEmpty(endTime)) {
            whereClause.append("and ic.createtime <= :endTime").append(" ");
            params.put("endTime", endTime);
        }

        if (whereClause.length() > 0) {
            sql += whereClause.substring(3, whereClause.length());
        } else {
            sql = sql.replace("where", "");
            sql = sql.replace("WHERE", "");
        }

        Long cnt = countBySql(sql, params);
        List<Map<String, Object>> list = cnt > 0 ? getTagChangeRecordDataList(userId, deptId, changeType, type, itemId, keyword, state, shUserId, startTime, endTime, pageNum, pageSize) : null;
        Map<String, Object> data = new HashMap<>();
        data.put("count", cnt);
        data.put("list", list);
        response.setStatus(0);
        response.setData(data);
        return response;
    }

    @Override
    public List<Map<String, Object>> getTagChangeRecordDataList(String userId, String deptId, String changeType, String type, String itemId, String keyword, String state, String shUserId, String startTime, String endTime, Integer pageNum, Integer pageSize) throws Exception {
        String sql = " select ic.* from tag_change_record ic left join tbl_user u on u.id = ic.userid " +
                " where";
        StringBuilder whereClause = new StringBuilder();
        Map<String, Object> params = new HashMap<>();

        if (StringUtils.isNotEmpty(userId)) {
            whereClause.append("and ic.userid = :userId").append(" ");
            params.put("userId", userId);
        }

        if (StringUtils.isNotEmpty(keyword)) {
            whereClause.append("and  (ic.itemname like :keyword)").append(" ");
            params.put("keyword", "%" + keyword + "%");
        }

        if (StringUtils.isNotEmpty(changeType)) {
            whereClause.append("and ic.changeType = :changeType").append(" ");
            params.put("changeType", changeType);
        }

        if (StringUtils.isNotEmpty(type)) {
            whereClause.append("and ic.type = :type").append(" ");
            params.put("type", type);
        }

        if (StringUtils.isNotEmpty(itemId)) {
            whereClause.append("and ic.itemid = :itemId").append(" ");
            params.put("itemId", itemId);
        }

        if (StringUtils.isNotEmpty(state)) {
            whereClause.append("and ic.state in (:stateList)").append(" ");
            params.put("stateList", state.split(","));
        }

        if (StringUtils.isNotEmpty(startTime)) {
            whereClause.append("and ic.createtime >= :startTime").append(" ");
            params.put("startTime", startTime);
        }
        if (StringUtils.isNotEmpty(endTime)) {
            whereClause.append("and ic.createtime <= :endTime").append(" ");
            params.put("endTime", endTime);
        }

        if (whereClause.length() > 0) {
            sql += whereClause.substring(3, whereClause.length());
        } else {
            sql = sql.replace("where", "");
            sql = sql.replace("WHERE", "");
        }

        sql += " order by  ic.createtime desc";
        List<Map<String, Object>> list = null;
        if (pageSize != null && pageSize > 0 && pageNum != null && pageNum > 0) {
            list = findMapBySql(sql, params, pageNum, pageSize);
        } else {
            list = findMapBySql(sql, params);
        }
        return list;
    }

    @Override
    public BaseResponse updateTagChangeRecordState(String userId, String id, int state, String reason) throws Exception {
        BaseResponse response = new BaseResponse();

        User user = get(User.class, userId);
        if (user == null) {
            response.setStatus(1);
            response.setMessage("用户不存在！");
            return response;
        }
        TagChangeRecord tagChangeRecord = get(TagChangeRecord.class, id);
        if (state == 1 || state == -1) {
            if (tagChangeRecord.getState() != 0) {
                response.setStatus(1);
                response.setMessage("操作无法进行，请注意申请的状态！");
                return response;
            }
            tagChangeRecord.setState(state);
            tagChangeRecord.setDefaultUpdateTime();
            tagChangeRecord.setDefaultShTime();
            tagChangeRecord.setShUserId(userId);
            tagChangeRecord.setShUserName(user.getName());
            tagChangeRecord.setReason(reason);
            update(tagChangeRecord);

            System.out.println("state:" + state);
            if (state == 1) {
                System.out.println("changeType:" + tagChangeRecord.getChangeType());
                if (tagChangeRecord.getChangeType() == 0) {
                    String dataaf = tagChangeRecord.getDataaf();
                    UserTag userTag = new Gson().fromJson(dataaf, UserTag.class);
                    userTag.setDefaultTime();
                    save(userTag);
                } else {
                    String dataaf = tagChangeRecord.getDataaf();
                    UserTag userTag = null;
                    if (StringUtils.isEmpty(dataaf)) {
                        userTag = new UserTag();
                        userTag.setTagtype(tagChangeRecord.getToPost());
                        userTag.setTagstarttime(tagChangeRecord.getChangeTime());

                        userTag.setTagname("本公司");
                    } else {
                        userTag = new Gson().fromJson(dataaf, UserTag.class);
                    }

                    userTag.setDefaultTime();
                    userTag.setType("5");

                    String itemid = tagChangeRecord.getItemid();
                    String userName = tagChangeRecord.getItemname();
                    String deptId = tagChangeRecord.getToDeptId();
                    User newUser = null;
                    if (tagChangeRecord.getType() == 10) {
                        newUser = new User();
                        newUser.setName(userName);
                        newUser.setDeptId(deptId);
                        save(newUser);

                    } else {
                        newUser = get(User.class, itemid);
                        newUser.setDeptId(deptId);
                        update(newUser);
                    }


                    userTag.setUserId(newUser.getId());
                    if (tagChangeRecord.getType() == 10 || tagChangeRecord.getType() == 11 ||
                            tagChangeRecord.getType() == 12 || tagChangeRecord.getType() == 13) {
                        save(userTag);
                    }
                }

            }
        } else {
            response.setStatus(1);
            response.setMessage("操作无法进行，请注意检查参数！");
            return response;

        }

        return response;
    }


    @Override
    public BaseResponse getTagChangeDAList(String year, String month) throws Exception {
        BaseResponse response = new BaseResponse();
        String startTime = year + "-" + month + "-01";
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(DateUtil.parse(startTime, DateUtil.YYYY_MM_DD));
        calendar.add(Calendar.MONTH, 1);
        calendar.add(Calendar.DATE, -1);
        String endTime = DateUtil.format(calendar.getTime(), DateUtil.YYYY_MM_DD) + " 23:59:59";
        List<Map<String, Object>> list = new ArrayList<>();

        Map<String, Object> item10 = new HashMap<>();
        item10.put("changeType", 1);
        item10.put("type", 10);
        item10.put("ut", "入职");
        item10.put("cnt", 0);
        item10.put("startTime", startTime);
        item10.put("endTime", endTime);
        list.add(item10);

        Map<String, Object> item11 = new HashMap<>();
        item11.put("changeType", 1);
        item11.put("type", 11);
        item11.put("ut", "转正");
        item11.put("cnt", 0);
        item11.put("startTime", startTime);
        item11.put("endTime", endTime);
        list.add(item11);

        Map<String, Object> item12 = new HashMap<>();
        item12.put("changeType", 1);
        item12.put("type", 12);
        item12.put("ut", "任免");
        item12.put("cnt", 0);
        item12.put("startTime", startTime);
        item12.put("endTime", endTime);
        list.add(item12);

        Map<String, Object> item13 = new HashMap<>();
        item13.put("changeType", 1);
        item13.put("type", 13);
        item13.put("ut", "调动");
        item13.put("cnt", 0);
        item13.put("startTime", startTime);
        item13.put("endTime", endTime);
        list.add(item13);

        Map<String, Object> item14 = new HashMap<>();
        item14.put("changeType", 1);
        item14.put("type", 14);
        item14.put("ut", "离职");
        item14.put("cnt", 0);
        item14.put("startTime", startTime);
        item14.put("endTime", endTime);
        list.add(item14);

        Map<String, Object> item15 = new HashMap<>();
        item15.put("changeType", 1);
        item15.put("type", 15);
        item15.put("ut", "退休");
        item15.put("cnt", 0);
        item15.put("startTime", startTime);
        item15.put("endTime", endTime);
        list.add(item15);

        Map<String, Object> item1 = new HashMap<>();
        item1.put("changeType", 0);
        item1.put("type", 1);
        item1.put("ut", "学历");
        item1.put("cnt", 0);
        item1.put("startTime", startTime);
        item1.put("endTime", endTime);
        list.add(item1);

        Map<String, Object> item2 = new HashMap<>();
        item2.put("changeType", 0);
        item2.put("type", 2);
        item2.put("ut", "职称");
        item2.put("cnt", 0);
        item2.put("startTime", startTime);
        item2.put("endTime", endTime);
        list.add(item2);

        Map<String, Object> item3 = new HashMap<>();
        item3.put("changeType", 0);
        item3.put("type", 3);
        item3.put("ut", "职业资格");
        item3.put("cnt", 0);
        item3.put("startTime", startTime);
        item3.put("endTime", endTime);
        list.add(item3);

        String sql = "select changeType,type,count(*) cnt from tag_change_record where createtime >= :startTime and createtime <= :endTime " +
                " group by changeType,type";
        Map<String, Object> params = new HashMap<>();
        params.put("startTime", startTime);
        params.put("endTime", endTime);
        List<Map<String, Object>> dataList = findMapBySql(sql, params);
        if (dataList != null && dataList.size() > 0) {
            for (Map<String, Object> map : dataList) {
                if ("1".equals(map.get("changeType").toString())) {
                    if ("10".equals(map.get("type").toString())) {
                        Map<String, Object> item = list.get(0);
                        item.put("cnt", map.get("cnt") == null ? 0 : map.get("cnt"));
                    } else if ("11".equals(map.get("type").toString())) {
                        Map<String, Object> item = list.get(1);
                        item.put("cnt", map.get("cnt") == null ? 0 : map.get("cnt"));
                    } else if ("12".equals(map.get("type").toString())) {
                        Map<String, Object> item = list.get(2);
                        item.put("cnt", map.get("cnt") == null ? 0 : map.get("cnt"));
                    } else if ("13".equals(map.get("type").toString())) {
                        Map<String, Object> item = list.get(3);
                        item.put("cnt", map.get("cnt") == null ? 0 : map.get("cnt"));
                    } else if ("14".equals(map.get("type").toString())) {
                        Map<String, Object> item = list.get(4);
                        item.put("cnt", map.get("cnt") == null ? 0 : map.get("cnt"));
                    } else if ("15".equals(map.get("type").toString())) {
                        Map<String, Object> item = list.get(5);
                        item.put("cnt", map.get("cnt") == null ? 0 : map.get("cnt"));
                    }
                } else if ("0".equals(map.get("changeType").toString())) {
                    if ("1".equals(map.get("type").toString())) {
                        Map<String, Object> item = list.get(6);
                        item.put("cnt", map.get("cnt") == null ? 0 : map.get("cnt"));
                    } else if ("2".equals(map.get("type").toString())) {
                        Map<String, Object> item = list.get(7);
                        item.put("cnt", map.get("cnt") == null ? 0 : map.get("cnt"));
                    } else if ("3".equals(map.get("type").toString())) {
                        Map<String, Object> item = list.get(8);
                        item.put("cnt", map.get("cnt") == null ? 0 : map.get("cnt"));
                    }
                }
            }
        }
        response.setData(list);
        return response;
    }




    public String getFirstPinYin(String hanyu) {
        HanyuPinyinOutputFormat format = new HanyuPinyinOutputFormat();
        format.setCaseType(HanyuPinyinCaseType.LOWERCASE);
        format.setToneType(HanyuPinyinToneType.WITHOUT_TONE);

        StringBuilder firstPinyin = new StringBuilder();
        char[] hanyuArr = hanyu.trim().toCharArray();
        try {
            for (int i = 0, len = hanyuArr.length; i < len; i++) {
                if (Character.toString(hanyuArr[i]).matches("[\\u4E00-\\u9FA5]+")) {
                    String[] pys = PinyinHelper.toHanyuPinyinStringArray(hanyuArr[i], format);
                    firstPinyin.append(pys[0].charAt(0));
                } else {
                    firstPinyin.append(hanyuArr[i]);
                }
            }
        } catch (BadHanyuPinyinOutputFormatCombination badHanyuPinyinOutputFormatCombination) {
            badHanyuPinyinOutputFormatCombination.printStackTrace();
        }
        return firstPinyin.toString();
    }

    @Override
    public BaseResponse resetUserPwd(List<String> userIds) throws Exception {
        String sql = "select id,login_name from tbl_user";
        Map<String, Object> params = new HashMap<>();

        if (userIds != null && userIds.size() > 0) {
            sql += " where id in(:userIds)";
            params.put("userIds", userIds);
        }

        List<Map<String, Object>> userList = findMapBySql(sql, params);
        if (userList != null && userList.size() > 0) {
            sql = "update  tbl_user set password = :password where id = :id";
            String password = "fy369YH*";
            params.clear();
            for (Map<String, Object> user : userList) {
                String id = user.get("id").toString();
                String loginName = user.get("login_name").toString();
                params.put("id", id);
                params.put("password", EncryptUtil.getPassword(password, loginName));
                executeSql(sql, params);

            }
        }
        return new BaseResponse();
    }




    @Override
    public BaseResponse addAnswerTemp() throws Exception {
        BaseResponse baseResponse = new BaseResponse();
        String sql = "select sa.*,s.survey_name,sq.ques_name,sq.ques_seq FROM t_survey_answer sa " +
                " left join  t_survey s on s.id=sa.survey_id " +
                " left join  t_suevey_ques sq on sq.id=sa.ques_id " +
                " where  sa.user_id in " +
                "   (select DISTINCT user_id from" +
                "       (select survey_id,user_id,count(*) cnt from t_survey_answer where createDate>'2024' group by survey_id,user_id ) c " +
                "   where LOCATE('-',user_id)>0 and cnt>=10)";
        Map<String, Object> params = new HashMap<>();
        List<Map<String, Object>> list = findMapBySql(sql, params);

        sql = "insert into app_answer_temp(saId,createDate,updateDate,survey_id,ques_id,seq,value,user_id,survey_name,ques_name,ques_seq) " +
                " values (:ID,:createDate,:updateDate,:survey_id,:ques_id,:seq,:value,:user_id,:survey_name,:ques_name,:ques_seq)";

        String infoSql = "select item_code from t_survey_user_info where where ques_code = :quesCode and survey_user_id=:survey_user_id";
        for (Map<String, Object> item : list) {
            executeSql(sql,item);
        }
        return baseResponse;
    }


    public BaseResponse addAnswerTemp1() throws Exception {
        BaseResponse baseResponse = new BaseResponse();
        String sql = " insert into app_answer_temp(saId,createDate,updateDate,survey_id,ques_id,seq,value,user_id,survey_name,ques_name,ques_seq) " +
                " select sa.id,sa.createDate,sa.updateDate,sa.survey_id,sa.ques_id,sa.seq,sa.value,sa.user_id,s.survey_name,sq.ques_name,sq.ques_seq FROM t_survey_answer sa " +
                " left join  t_survey s on s.id=sa.survey_id " +
                " left join  t_suevey_ques sq on sq.id=sa.ques_id " +
                " where  sa.user_id in " +
                "   (select DISTINCT user_id from" +
                "       (select survey_id,user_id,count(*) cnt from t_survey_answer where createDate>'2024' group by survey_id,user_id ) c " +
                "   where LOCATE('-',user_id)>0 and cnt>=10)";
        Map<String, Object> params = new HashMap<>();
        List<Map<String, Object>> list = findMapBySql(sql, params);

        sql = "insert into app_answer_temp(saId,createDate,updateDate,survey_id,ques_id,seq,value,user_id,survey_name,ques_name,ques_seq) " +
                " values (:ID,:createDate,:updateDate,:survey_id,:ques_id,:seq,:value,:user_id,:survey_name,:ques_name,:ques_seq)";

        String infoSql = "select item_code from t_survey_user_info where where ques_code = :quesCode and survey_user_id=:survey_user_id";
        for (Map<String, Object> item : list) {
            executeSql(sql,item);
        }
        return baseResponse;
    }



    @Override
    public BaseResponse updateAnswerTempUserInfo() throws Exception {
        BaseResponse baseResponse = new BaseResponse();
        String sql = "select distinct user_id from  app_answer_temp  where item_level is null limit 10000";

        List<Map<String, Object>> list = findMapBySql(sql);

        sql = "update app_answer_temp set item_level=:itemLevel,item_age=:itemAge where user_id =:userId ";
        Map<String, Object> params = new HashMap<>();

        String infoSql = "select item_code from t_survey_user_info where ques_code = :quesCode and survey_user_id=:userId order by id desc limit 1";
        Map<String, Object> infoParams = new HashMap<>();
        for (Map<String, Object> item : list) {
            String userId = item.get("user_id").toString();
            infoParams.put("userId",userId);
            infoParams.put("quesCode","ZRYJ_LEVEL");
            String itemLevel = getBySql(infoSql,infoParams);

            infoParams.put("quesCode","ZRYJ_AGE");
            String itemAge = getBySql(infoSql,infoParams);

            params.put("itemLevel",itemLevel);
            params.put("itemAge",itemAge);
            params.put("userId",userId);
            executeSql(sql,params);

        }
        return baseResponse;
    }

    @Override
    public BaseResponse getWjList240925(String userId) throws Exception {
        BaseResponse baseResponse = new BaseResponse();
        String sql = "select * from tbl_dict where code = :code order by levelCode asc limit 1";
        Map<String, Object> params = new HashMap<>();
        params.put("code","wjxz");
        Dict cfgDict = getBySql(sql, params, Dict.class);
        if (cfgDict == null || StringUtils.isEmpty(cfgDict.getValue())) {
            baseResponse.setStatus(1);
            baseResponse.setMessage("问卷不存在！");
            return baseResponse;
        }
        String wjCode = cfgDict.getValue();
        params.put("code",wjCode);
        Dict rootDict = getBySql(sql, params, Dict.class);

        if (rootDict == null) {
            baseResponse.setStatus(1);
            baseResponse.setMessage("问卷不存在！");
            return baseResponse;
        }

        String startTime = rootDict.getValuex();
        String endTime = rootDict.getValuey();
        String curTime = DateUtil.format(new Date(),DateUtil.YYYY_MM_DD_HH_MM_SS);

        if (curTime.compareTo(startTime)<0 || curTime.compareTo(endTime)>0) {
            baseResponse.setStatus(1);
            baseResponse.setMessage("不在提交时间范围！");
            return baseResponse;
        }

        sql = "select d.*,ri.answCode,ri.tag,case when ri.id is null then 0 else 1 end tb  from tbl_dict d" +
                " left join (select * from app_wj_result_item where userId=:userId and createtime>:startTime and createtime<:endTime) ri on ri.quesCode = d.code " +
                " where d.levelCode like :levelCode order by levelCode asc";
        params.clear();
        params.put("levelCode",rootDict.getLevelCode()+"%");
        params.put("userId",userId);
        params.put("startTime",rootDict.getValuex());
        params.put("endTime",rootDict.getValuey());
        List<Map<String, Object>> list = findMapBySql(sql, params);

        Map<String, TreeNode> nodelist = new LinkedHashMap<String, TreeNode>();
        for (int i = 0; i < list.size(); i++) {
            Map<String, Object> item = list.get(i);
            String id = item.get("id").toString();
            String name = item.get("name").toString();
            String code = item.get("code").toString();
            String levelCode = item.get("levelCode").toString();
            String value = item.get("value")==null?null:item.get("value").toString();
            String valuex = item.get("valuex")==null?null:item.get("valuex").toString();
            String valuey = item.get("valuey")==null?null:item.get("valuey").toString();
            String tag = item.get("tag")==null?null:item.get("tag").toString();
            String answCode = item.get("answCode")==null?null:item.get("answCode").toString();
            String parentId = item.get("parent_id")==null?null:item.get("parent_id").toString();
            String tb = item.get("tb")==null?"0":item.get("tb").toString();


            if (i==0) {
                parentId = null;
            }
            TreeNode node = new TreeNode();
            node.setId(id);
            node.setText(name);

            node.setCode(code);
            node.setValue(value);
            node.setValuex(valuex);
            node.setValuey(valuey);
            node.setParentId(parentId);
            node.setLevelCode(levelCode);
            node.setTag(tag);
            node.setTag0(answCode);
            node.setIcon(tb);
            nodelist.put(node.getId(), node);
        }
        // 构造树形结构
        List<TreeNode> tnlist = TreeUtil.getNodeList(nodelist);

        baseResponse.setData(tnlist);
        return baseResponse;
    }

    @Override
    public BaseResponse saveWj240925(AppWjResult wjResult) throws Exception {

        String token = wjResult.getToken();

        BaseResponse baseResponse = checkToken(token);
        if (baseResponse.getStatus() != 0) {
            return baseResponse;
        }

        Map<String, Object> user = (Map<String, Object>) baseResponse.getData();
        int userId = Integer.parseInt(user.get("id").toString());
        String userName = user.get("mobile").toString();
        logger.error("saveWj240925:" + userId+":"+userName);
        baseResponse.setData(null);

        String wjCode = wjResult.getWjCode();
        String sql = "select * from tbl_dict where code = :code order by levelCode asc limit 1";
        Map<String, Object> params = new HashMap<>();
        params.put("code",wjCode);
        Dict rootDict = getBySql(sql, params, Dict.class);
        if (rootDict == null) {
            baseResponse.setStatus(1);
            baseResponse.setMessage("问卷不存在！");
            return baseResponse;
        }

        String startTime = rootDict.getValuex();
        String endTime = rootDict.getValuey();
        String curTime = DateUtil.format(new Date(),DateUtil.YYYY_MM_DD_HH_MM_SS);

        if (curTime.compareTo(startTime)<0 || curTime.compareTo(endTime)>0) {
            baseResponse.setStatus(1);
            baseResponse.setMessage("不在提交时间范围！");
            return baseResponse;
        }

        sql = "select * from app_wj_result where wjCode = :code and userId = :userId";
        params.put("userId",userId);
        AppWjResult oldWjResult = getBySql(sql, params, AppWjResult.class);

        List<AppWjResultItem> wjResultItemList = wjResult.getWjResultItemList();
        Long mainId = null;
        if (oldWjResult == null) {
            wjResult.setDefaultTime();
            wjResult.setUserId(userId);
            wjResult.setUserName(userName);
            wjResult.setWjName(rootDict.getName());
            save(wjResult);
            mainId = wjResult.getId();
        }else {
            baseResponse.setStatus(1);
            baseResponse.setMessage("您已参与过本次问卷，请勿重复提交！");
            return baseResponse;

        }
        String sql1 = "select name from tbl_dict where code = :code order by levelCode asc limit 1";
        //String sql2 = "select group_concat(concat(value,':',name) order by code asc)  as name,group_concat(code order by code asc)  as code from tbl_dict where code in (:code)";
        String sql2 = "select group_concat(concat(value,'：',name) order by code asc)  as name from tbl_dict where code in (:code)";
        params.clear();

        int totalPoint = 0;
        for (AppWjResultItem appWjResultItem : wjResultItemList) {
            appWjResultItem.setDefaultTime();
            appWjResultItem.setMainId(mainId);
            appWjResultItem.setUserId(wjResult.getUserId());
            appWjResultItem.setUserName(wjResult.getUserName());
            appWjResultItem.setWjCode(wjResult.getWjCode());
            appWjResultItem.setWjName(wjResult.getWjName());

            params.put("code",appWjResultItem.getQuesCode());
            String quesText = getBySql(sql1, params);
            appWjResultItem.setQuesText(quesText);

            String answCode = appWjResultItem.getAnswCode();

            params.put("code",answCode.split(","));
            //Map<String, Object> answMap = getMapBySql(sql2, params);
            //String answText = answMap.get("name")==null?null:answMap.get("name").toString();
            //String newAnswCode = answMap.get("name")==null?null:answMap.get("name").toString();
            String answText =getBySql(sql2,params);
            appWjResultItem.setAnswText(answText);

            /*Integer point = null;
            if ("op_my".equals(answCode)) {
                point = 10;
            }else if ("op_bjmy".equals(answCode)) {
                point = 8;
            }else if ("op_yb".equals(answCode)) {
                point = 6;
            }else if ("op_bmy".equals(answCode)) {
                point = 0;
            }
            appWjResultItem.setPoint(point);

            totalPoint+=(point==null?0:point);*/
        }
        batchSave(wjResultItemList);
        /*wjResult.setPoint(totalPoint);
        update(wjResult);*/

        return baseResponse;
    }

    @Override
    public BaseResponse getWjList(String userId) throws Exception {
        BaseResponse baseResponse = new BaseResponse();
        String sql = "select * from tbl_dict where code = :code order by levelCode asc limit 1";
        Map<String, Object> params = new HashMap<>();
        params.put("code","wjxz");
        Dict cfgDict = getBySql(sql, params, Dict.class);
        if (cfgDict == null || StringUtils.isEmpty(cfgDict.getValue())) {
            baseResponse.setStatus(1);
            baseResponse.setMessage("问卷不存在！");
            return baseResponse;
        }
        String wjCode = cfgDict.getValue();
        params.put("code",wjCode);
        Dict rootDict = getBySql(sql, params, Dict.class);

        if (rootDict == null) {
            baseResponse.setStatus(1);
            baseResponse.setMessage("问卷不存在！");
            return baseResponse;
        }

        sql = "select d.*,ri.answCode,ri.tag,case when ri.id is null then 0 else 1 end tb  from tbl_dict d" +
                " left join (select * from app_wj_result_item where userId=:userId and createtime>:startTime and createtime<:endTime) ri on ri.quesCode = d.code " +
                " where d.levelCode like :levelCode order by levelCode asc";
        params.clear();
        params.put("levelCode",rootDict.getLevelCode()+"%");
        params.put("userId",userId);
        params.put("startTime",rootDict.getValuex());
        params.put("endTime",rootDict.getValuey());
        List<Map<String, Object>> list = findMapBySql(sql, params);

        Map<String, TreeNode> nodelist = new LinkedHashMap<String, TreeNode>();
        for (int i = 0; i < list.size(); i++) {
            Map<String, Object> item = list.get(i);
            String id = item.get("id").toString();
            String name = item.get("name").toString();
            String code = item.get("code").toString();
            String levelCode = item.get("levelCode").toString();
            //String value = item.get("value")==null?null:item.get("value").toString();
            String valuex = item.get("valuex")==null?null:item.get("valuex").toString();
            String valuey = item.get("valuey")==null?null:item.get("valuey").toString();
            String tag = item.get("tag")==null?null:item.get("tag").toString();
            String answCode = item.get("answCode")==null?null:item.get("answCode").toString();
            String parentId = item.get("parent_id")==null?null:item.get("parent_id").toString();
            String tb = item.get("tb")==null?"0":item.get("tb").toString();


            if (i==0) {
                parentId = null;
            }
            TreeNode node = new TreeNode();
            node.setId(id);
            node.setText(name);

            node.setCode(code);
            //node.setValue(value);
            node.setValuex(valuex);
            node.setValuey(valuey);
            node.setParentId(parentId);
            node.setLevelCode(levelCode);
            node.setTag(tag);
            node.setTag0(answCode);
            node.setIcon(tb);
            nodelist.put(node.getId(), node);
        }
        // 构造树形结构
        List<TreeNode> tnlist = TreeUtil.getNodeList(nodelist);

        baseResponse.setData(tnlist);
        return baseResponse;
    }

    @Override
    public BaseResponse saveWj(AppWjResult wjResult) throws Exception {

        String token = wjResult.getToken();

        BaseResponse baseResponse = checkToken(token);
        if (baseResponse.getStatus() != 0) {
            return baseResponse;
        }

        Map<String, Object> user = (Map<String, Object>) baseResponse.getData();
        int userId = Integer.parseInt(user.get("id").toString());
        String userName = user.get("mobile").toString();
        logger.error("saveWj:" + userId+":"+userName);
        baseResponse.setData(null);

        String wjCode = wjResult.getWjCode();
        String sql = "select * from tbl_dict where code = :code order by levelCode asc limit 1";
        Map<String, Object> params = new HashMap<>();
        params.put("code",wjCode);
        Dict rootDict = getBySql(sql, params, Dict.class);
        if (rootDict == null) {
            baseResponse.setStatus(1);
            baseResponse.setMessage("问卷不存在！");
            return baseResponse;
        }

        String startTime = rootDict.getValuex();
        String endTime = rootDict.getValuey();
        String curTime = DateUtil.format(new Date(),DateUtil.YYYY_MM_DD_HH_MM_SS);

        if (curTime.compareTo(startTime)<0 || curTime.compareTo(endTime)>0) {
            baseResponse.setStatus(1);
            baseResponse.setMessage("不在提交时间范围！");
            return baseResponse;
        }

        sql = "select * from app_wj_result where wjCode = :code and userId = :userId";
        params.put("userId",userId);
        AppWjResult oldWjResult = getBySql(sql, params, AppWjResult.class);

        List<AppWjResultItem> wjResultItemList = wjResult.getWjResultItemList();
        Long mainId = null;
        if (oldWjResult == null) {
            wjResult.setDefaultTime();
            wjResult.setUserId(userId);
            wjResult.setUserName(userName);
            wjResult.setWjName(rootDict.getName());
            save(wjResult);
            mainId = wjResult.getId();
        }else {
            wjResult = oldWjResult;
            wjResult.setDefaultUpdateTime();

            mainId = wjResult.getId();
            sql = "delete from app_wj_result_item where mainId=:mainId";
            params.clear();
            params.put("mainId",mainId);
            executeSql(sql,params);

        }
        sql = "select name from tbl_dict where code = :code order by levelCode asc limit 1";
        params.clear();

        int totalPoint = 0;
        for (AppWjResultItem appWjResultItem : wjResultItemList) {
            appWjResultItem.setDefaultTime();
            appWjResultItem.setMainId(mainId);
            appWjResultItem.setUserId(wjResult.getUserId());
            appWjResultItem.setUserName(wjResult.getUserName());
            appWjResultItem.setWjCode(wjResult.getWjCode());
            appWjResultItem.setWjName(wjResult.getWjName());

            params.put("code",appWjResultItem.getQuesCode());
            String quesText = getBySql(sql, params);
            appWjResultItem.setQuesText(quesText);

            String answCode = appWjResultItem.getAnswCode();
            Integer point = null;
            if ("op_my".equals(answCode)) {
                point = 10;
            }else if ("op_bjmy".equals(answCode)) {
                point = 8;
            }else if ("op_yb".equals(answCode)) {
                point = 6;
            }else if ("op_bmy".equals(answCode)) {
                point = 0;
            }
            appWjResultItem.setPoint(point);

            totalPoint+=(point==null?0:point);
        }
        batchSave(wjResultItemList);
        wjResult.setPoint(totalPoint);
        update(wjResult);

        return baseResponse;
    }

    @Override
    public BaseResponse getWjResultList(String userId, String wjCode, Integer pointMin, Integer pointMax, String deptId, String type, String state, String keyword, String startTime, String endTime, Integer pageNum, Integer pageSize) throws Exception {
        BaseResponse response = new BaseResponse();
        String sql = "select count(*) from app_wj_result ic " +
                " left join app_user u on ic.userId = u.id " +
                " where";
        StringBuilder whereClause = new StringBuilder();
        Map<String, Object> params = new HashMap<>();

        if (StringUtils.isNotEmpty(userId)) {
            whereClause.append("and ic.userid = :userId").append(" ");
            params.put("userId", userId);
        }

        if (StringUtils.isNotEmpty(wjCode)) {
            whereClause.append("and ic.wjCode = :wjCode").append(" ");
            params.put("wjCode", wjCode);
        }

        if (StringUtils.isNotEmpty(keyword)) {
            whereClause.append("and  (ic.wjName like :keyword or ic.userName like :keyword)").append(" ");
            params.put("keyword", "%" + keyword + "%");
        }

        if (pointMin!=null) {
            whereClause.append("and ic.point >= :pointMin").append(" ");
            params.put("pointMin", pointMin);
        }

        if (pointMax!=null) {
            whereClause.append("and ic.point <= :pointMax").append(" ");
            params.put("pointMax", pointMax);
        }

        if (StringUtils.isNotEmpty(deptId)) {
            whereClause.append("and u.deptId = :deptId").append(" ");
            params.put("deptId", deptId);
        }

        if (StringUtils.isNotEmpty(type)) {
            whereClause.append("and u.type = :type").append(" ");
            params.put("type", type);
        }

        if (StringUtils.isNotEmpty(state)) {
            whereClause.append("and ic.state in (:stateList)").append(" ");
            params.put("stateList", state.split(","));
        }



        if (StringUtils.isNotEmpty(startTime)) {
            whereClause.append("and ic.createtime >= :startTime").append(" ");
            params.put("startTime", startTime);
        }
        if (StringUtils.isNotEmpty(endTime)) {
            whereClause.append("and ic.createtime <= :endTime").append(" ");
            params.put("endTime", endTime);
        }

        if (whereClause.length() > 0) {
            sql += whereClause.substring(3, whereClause.length());
        } else {
            sql = sql.replace("where", "");
            sql = sql.replace("WHERE", "");
        }

        Long cnt = countBySql(sql, params);
        List<Map<String, Object>> list = cnt > 0 ? getWjResultDataList(userId, wjCode, pointMin, pointMax, deptId, type, state, keyword, startTime, endTime, pageNum, pageSize) : null;
        Map<String, Object> data = new HashMap<>();
        data.put("count", cnt);
        data.put("list", list);
        response.setStatus(0);
        response.setData(data);
        return response;
    }

    @Override
    public List<Map<String, Object>> getWjResultDataList(String userId, String wjCode, Integer pointMin, Integer pointMax, String deptId, String type, String state, String keyword, String startTime, String endTime, Integer pageNum, Integer pageSize) throws Exception {
        String sql = " select ic.*,u.type,u.deptName from app_wj_result ic " +
                " left join app_user u on ic.userId = u.id " +
                " where";
        StringBuilder whereClause = new StringBuilder();
        Map<String, Object> params = new HashMap<>();

        if (StringUtils.isNotEmpty(userId)) {
            whereClause.append("and ic.userid = :userId").append(" ");
            params.put("userId", userId);
        }

        if (StringUtils.isNotEmpty(wjCode)) {
            whereClause.append("and ic.wjCode = :wjCode").append(" ");
            params.put("wjCode", wjCode);
        }

        if (StringUtils.isNotEmpty(keyword)) {
            whereClause.append("and  (ic.wjName like :keyword or ic.userName like :keyword)").append(" ");
            params.put("keyword", "%" + keyword + "%");
        }

        if (pointMin!=null) {
            whereClause.append("and ic.point >= :pointMin").append(" ");
            params.put("pointMin", pointMin);
        }

        if (pointMax!=null) {
            whereClause.append("and ic.point <= :pointMax").append(" ");
            params.put("pointMax", pointMax);
        }

        if (StringUtils.isNotEmpty(deptId)) {
            whereClause.append("and u.deptId = :deptId").append(" ");
            params.put("deptId", deptId);
        }

        if (StringUtils.isNotEmpty(type)) {
            whereClause.append("and u.type = :type").append(" ");
            params.put("type", type);
        }

        if (StringUtils.isNotEmpty(state)) {
            whereClause.append("and ic.state in (:stateList)").append(" ");
            params.put("stateList", state.split(","));
        }

        if (StringUtils.isNotEmpty(startTime)) {
            whereClause.append("and ic.createtime >= :startTime").append(" ");
            params.put("startTime", startTime);
        }
        if (StringUtils.isNotEmpty(endTime)) {
            whereClause.append("and ic.createtime <= :endTime").append(" ");
            params.put("endTime", endTime);
        }

        if (whereClause.length() > 0) {
            sql += whereClause.substring(3, whereClause.length());
        } else {
            sql = sql.replace("where", "");
            sql = sql.replace("WHERE", "");
        }

        sql += " order by ic.createtime desc";
        List<Map<String, Object>> list = null;
        if (pageSize != null && pageSize > 0 && pageNum != null && pageNum > 0) {
            list = findMapBySql(sql, params, pageNum, pageSize);
        } else {
            list = findMapBySql(sql, params);
        }
        return list;
    }


    @Override
    public BaseResponse getWjResultItemList(String mainId,String userId, String wjCode, Integer pointMin, Integer pointMax, String deptId, String type, String state, String keyword, String startTime, String endTime, Integer pageNum, Integer pageSize) throws Exception {
        BaseResponse response = new BaseResponse();
        String sql = "select count(*) from app_wj_result_item ic " +
                " left join app_user u on ic.userId = u.id " +
                " where";
        StringBuilder whereClause = new StringBuilder();
        Map<String, Object> params = new HashMap<>();

        if (StringUtils.isNotEmpty(userId)) {
            whereClause.append("and ic.mainId = :mainId").append(" ");
            params.put("mainId", mainId);
        }

        if (StringUtils.isNotEmpty(userId)) {
            whereClause.append("and ic.userid = :userId").append(" ");
            params.put("userId", userId);
        }

        if (StringUtils.isNotEmpty(wjCode)) {
            whereClause.append("and ic.wjCode = :wjCode").append(" ");
            params.put("wjCode", wjCode);
        }

        if (StringUtils.isNotEmpty(keyword)) {
            whereClause.append("and  (ic.wjName like :keyword or ic.userName like :keyword)").append(" ");
            params.put("keyword", "%" + keyword + "%");
        }

        if (pointMin!=null) {
            whereClause.append("and ic.point >= :pointMin").append(" ");
            params.put("pointMin", pointMin);
        }

        if (pointMax!=null) {
            whereClause.append("and ic.point <= :pointMax").append(" ");
            params.put("pointMax", pointMax);
        }

        if (StringUtils.isNotEmpty(deptId)) {
            whereClause.append("and u.deptId = :deptId").append(" ");
            params.put("deptId", deptId);
        }

        if (StringUtils.isNotEmpty(type)) {
            whereClause.append("and u.type = :type").append(" ");
            params.put("type", type);
        }

        if (StringUtils.isNotEmpty(state)) {
            whereClause.append("and ic.state in (:stateList)").append(" ");
            params.put("stateList", state.split(","));
        }



        if (StringUtils.isNotEmpty(startTime)) {
            whereClause.append("and ic.createtime >= :startTime").append(" ");
            params.put("startTime", startTime);
        }
        if (StringUtils.isNotEmpty(endTime)) {
            whereClause.append("and ic.createtime <= :endTime").append(" ");
            params.put("endTime", endTime);
        }

        if (whereClause.length() > 0) {
            sql += whereClause.substring(3, whereClause.length());
        } else {
            sql = sql.replace("where", "");
            sql = sql.replace("WHERE", "");
        }

        Long cnt = countBySql(sql, params);
        List<Map<String, Object>> list = cnt > 0 ? getWjResultItemDataList( mainId,userId, wjCode, pointMin, pointMax, deptId, type, state, keyword, startTime, endTime, pageNum, pageSize) : null;
        Map<String, Object> data = new HashMap<>();
        data.put("count", cnt);
        data.put("list", list);
        response.setStatus(0);
        response.setData(data);
        return response;
    }

    @Override
    public List<Map<String, Object>> getWjResultItemDataList(String mainId,String userId, String wjCode, Integer pointMin, Integer pointMax, String deptId, String type, String state, String keyword, String startTime, String endTime, Integer pageNum, Integer pageSize) throws Exception {
        String sql = " select ic.*,u.type,u.deptName from app_wj_result_item ic " +
                " left join app_user u on ic.userId = u.id " +
                " where";
        StringBuilder whereClause = new StringBuilder();
        Map<String, Object> params = new HashMap<>();

        if (StringUtils.isNotEmpty(userId)) {
            whereClause.append("and ic.mainId = :mainId").append(" ");
            params.put("mainId", mainId);
        }

        if (StringUtils.isNotEmpty(userId)) {
            whereClause.append("and ic.userid = :userId").append(" ");
            params.put("userId", userId);
        }

        if (StringUtils.isNotEmpty(wjCode)) {
            whereClause.append("and ic.wjCode = :wjCode").append(" ");
            params.put("wjCode", wjCode);
        }

        if (StringUtils.isNotEmpty(keyword)) {
            whereClause.append("and  (ic.wjName like :keyword or ic.userName like :keyword)").append(" ");
            params.put("keyword", "%" + keyword + "%");
        }

        if (pointMin!=null) {
            whereClause.append("and ic.point >= :pointMin").append(" ");
            params.put("pointMin", pointMin);
        }

        if (pointMax!=null) {
            whereClause.append("and ic.point <= :pointMax").append(" ");
            params.put("pointMax", pointMax);
        }

        if (StringUtils.isNotEmpty(deptId)) {
            whereClause.append("and u.deptId = :deptId").append(" ");
            params.put("deptId", deptId);
        }

        if (StringUtils.isNotEmpty(type)) {
            whereClause.append("and u.type = :type").append(" ");
            params.put("type", type);
        }

        if (StringUtils.isNotEmpty(state)) {
            whereClause.append("and ic.state in (:stateList)").append(" ");
            params.put("stateList", state.split(","));
        }

        if (StringUtils.isNotEmpty(startTime)) {
            whereClause.append("and ic.createtime >= :startTime").append(" ");
            params.put("startTime", startTime);
        }
        if (StringUtils.isNotEmpty(endTime)) {
            whereClause.append("and ic.createtime <= :endTime").append(" ");
            params.put("endTime", endTime);
        }

        if (whereClause.length() > 0) {
            sql += whereClause.substring(3, whereClause.length());
        } else {
            sql = sql.replace("where", "");
            sql = sql.replace("WHERE", "");
        }

        sql += " order by ic.mainId desc,ic.quesCode asc";
        List<Map<String, Object>> list = null;
        if (pageSize != null && pageSize > 0 && pageNum != null && pageNum > 0) {
            list = findMapBySql(sql, params, pageNum, pageSize);
        } else {
            list = findMapBySql(sql, params);
        }
        return list;
    }
    @Override
    public BaseResponse getAppUserList(String deptId,String type, String keyword, String startTime, String endTime, Integer pageNum, Integer pageSize) throws Exception {
        BaseResponse response = new BaseResponse();
        String sql = "select count(*) from app_user ic " +
                " where";
        StringBuilder whereClause = new StringBuilder();
        Map<String, Object> params = new HashMap<>();

        if (StringUtils.isNotEmpty(deptId)) {
            whereClause.append("and ic.deptId = :deptId").append(" ");
            params.put("deptId", deptId);
        }

        if (StringUtils.isNotEmpty(type)) {
            whereClause.append("and ic.type = :type").append(" ");
            params.put("type", type);
        }

        if (StringUtils.isNotEmpty(keyword)) {
            whereClause.append("and  (ic.mobile like :keyword or ic.userName like :keyword)").append(" ");
            params.put("keyword", "%" + keyword + "%");
        }


        if (StringUtils.isNotEmpty(startTime)) {
            whereClause.append("and ic.createtime >= :startTime").append(" ");
            params.put("startTime", startTime);
        }
        if (StringUtils.isNotEmpty(endTime)) {
            whereClause.append("and ic.createtime <= :endTime").append(" ");
            params.put("endTime", endTime);
        }

        if (whereClause.length() > 0) {
            sql += whereClause.substring(3, whereClause.length());
        } else {
            sql = sql.replace("where", "");
            sql = sql.replace("WHERE", "");
        }

        Long cnt = countBySql(sql, params);
        List<Map<String, Object>> list = cnt > 0 ? getAppUserDataList(deptId, type, keyword, startTime, endTime, pageNum, pageSize) : null;
        Map<String, Object> data = new HashMap<>();
        data.put("count", cnt);
        data.put("list", list);
        response.setStatus(0);
        response.setData(data);
        return response;
    }

    @Override
    public List<Map<String, Object>> getAppUserDataList(String deptId,String type, String keyword, String startTime, String endTime, Integer pageNum, Integer pageSize) throws Exception {
        String sql = " select ic.* from app_user ic " +
                " where";
        StringBuilder whereClause = new StringBuilder();
        Map<String, Object> params = new HashMap<>();

        if (StringUtils.isNotEmpty(deptId)) {
            whereClause.append("and ic.deptId = :deptId").append(" ");
            params.put("deptId", deptId);
        }

        if (StringUtils.isNotEmpty(type)) {
            whereClause.append("and ic.type = :type").append(" ");
            params.put("type", type);
        }

        if (StringUtils.isNotEmpty(keyword)) {
            whereClause.append("and  (ic.mobile like :keyword or ic.userName like :keyword)").append(" ");
            params.put("keyword", "%" + keyword + "%");
        }

        if (StringUtils.isNotEmpty(startTime)) {
            whereClause.append("and ic.createtime >= :startTime").append(" ");
            params.put("startTime", startTime);
        }
        if (StringUtils.isNotEmpty(endTime)) {
            whereClause.append("and ic.createtime <= :endTime").append(" ");
            params.put("endTime", endTime);
        }

        if (whereClause.length() > 0) {
            sql += whereClause.substring(3, whereClause.length());
        } else {
            sql = sql.replace("where", "");
            sql = sql.replace("WHERE", "");
        }

        sql += " order by ic.createtime desc";
        List<Map<String, Object>> list = null;
        if (pageSize != null && pageSize > 0 && pageNum != null && pageNum > 0) {
            list = findMapBySql(sql, params, pageNum, pageSize);
        } else {
            list = findMapBySql(sql, params);
        }
        return list;
    }


    @Override
    public BaseResponse getWjResult240925(String wjId) throws Exception {
        BaseResponse baseResponse = new BaseResponse();
        String sql = "select * from app_wj_result where id = :id";
        Map<String, Object> params = new HashMap<>();
        params.put("id", wjId);
        AppWjResult wjResult = getBySql(sql, params, AppWjResult.class);
        if (wjResult == null) {
            baseResponse.setStatus(1);
            baseResponse.setMessage("问卷不存在！");
            return baseResponse;
        }

        String wjCode = wjResult.getWjCode();

        sql = "select * from tbl_dict where code = :code order by levelCode asc limit 1";
        params.clear();
        params.put("code",wjCode);
        Dict rootDict = getBySql(sql, params, Dict.class);
        if (rootDict == null) {
            baseResponse.setStatus(1);
            baseResponse.setMessage("问卷不存在！");
            return baseResponse;
        }

        sql = "select d.id,d.name,d.code,d.levelCode,d.value,d.valuex,d.valuey,d.parent_id,ri.id riId,ri.answCode,ri.tag,case when ri.id is null then 0 else 1 end tb  from tbl_dict d" +
                " left join (select * from app_wj_result_item where mainId=:mainId) ri on locate(concat(d.code,','),concat(ri.answCode,','))>0 or (locate(concat(d.code,','),concat(ri.quesCode,','))>0 and d.value='2')" +
                " where d.levelCode like :levelCode order by levelCode asc";
        params.clear();
        params.put("levelCode",rootDict.getLevelCode()+"%");
        params.put("mainId",wjId);

        List<Map<String, Object>> list = findMapBySql(sql, params);

        Map<String, TreeNode> nodelist = new LinkedHashMap<String, TreeNode>();
        for (int i = 0; i < list.size(); i++) {
            Map<String, Object> item = list.get(i);
            String id = item.get("id").toString();
            String name = item.get("name").toString();
            String code = item.get("code").toString();
            String levelCode = item.get("levelCode").toString();
            String value = item.get("value")==null?null:item.get("value").toString();
            String valuex = item.get("valuex")==null?null:item.get("valuex").toString();
            String valuey = item.get("valuey")==null?null:item.get("valuey").toString();
            String tag = item.get("tag")==null?null:item.get("tag").toString();
            String answCode = item.get("answCode")==null?null:item.get("answCode").toString();
            String parentId = item.get("parent_id")==null?null:item.get("parent_id").toString();
            String tb = item.get("tb")==null?"0":item.get("tb").toString();


            if (i==0) {
                parentId = null;
            }
            TreeNode node = new TreeNode();
            node.setId(id);
            node.setText(name);

            node.setCode(code);
            node.setValue(value);
            node.setValuex(valuex);
            node.setValuey(valuey);
            node.setParentId(parentId);
            node.setLevelCode(levelCode);
            node.setTag(tag);
            node.setTag0(answCode);
            node.setIcon(tb);
            nodelist.put(node.getId(), node);
        }
        // 构造树形结构
        List<TreeNode> tnlist = TreeUtil.getNodeList(nodelist);

        baseResponse.setData(tnlist);
        return baseResponse;
    }
    @Override
    public BaseResponse getWjResult(String wjId) throws Exception {
        BaseResponse baseResponse = new BaseResponse();
        String sql = "select * from app_wj_result where id = :id";
        Map<String, Object> params = new HashMap<>();
        params.put("id", wjId);
        AppWjResult wjResult = getBySql(sql, params, AppWjResult.class);
        if (wjResult == null) {
            baseResponse.setStatus(1);
            baseResponse.setMessage("问卷不存在！");
            return baseResponse;
        }

        String wjCode = wjResult.getWjCode();

        sql = "select * from tbl_dict where code = :code order by levelCode asc limit 1";
        params.clear();
        params.put("code",wjCode);
        Dict rootDict = getBySql(sql, params, Dict.class);
        if (rootDict == null) {
            baseResponse.setStatus(1);
            baseResponse.setMessage("问卷不存在！");
            return baseResponse;
        }

        sql = "select d.*,ri.answCode,ri.tag,case when ri.id is null then 0 else 1 end tb  from tbl_dict d" +
                " left join (select * from app_wj_result_item where mainId=:mainId) ri on ri.quesCode = d.code " +
                " where d.levelCode like :levelCode order by levelCode asc";
        params.clear();
        params.put("levelCode",rootDict.getLevelCode()+"%");
        params.put("mainId",wjId);

        List<Map<String, Object>> list = findMapBySql(sql, params);

        Map<String, TreeNode> nodelist = new LinkedHashMap<String, TreeNode>();
        for (int i = 0; i < list.size(); i++) {
            Map<String, Object> item = list.get(i);
            String id = item.get("id").toString();
            String name = item.get("name").toString();
            String code = item.get("code").toString();
            String levelCode = item.get("levelCode").toString();
            String value = item.get("value")==null?null:item.get("value").toString();
            String valuex = item.get("valuex")==null?null:item.get("valuex").toString();
            String valuey = item.get("valuey")==null?null:item.get("valuey").toString();
            String tag = item.get("tag")==null?null:item.get("tag").toString();
            String answCode = item.get("answCode")==null?null:item.get("answCode").toString();
            String parentId = item.get("parent_id")==null?null:item.get("parent_id").toString();
            String tb = item.get("tb")==null?"0":item.get("tb").toString();


            if (i==0) {
                parentId = null;
            }
            TreeNode node = new TreeNode();
            node.setId(id);
            node.setText(name);

            node.setCode(code);
            node.setValue(value);
            node.setValuex(valuex);
            node.setValuey(valuey);
            node.setParentId(parentId);
            node.setLevelCode(levelCode);
            node.setTag(tag);
            node.setTag0(answCode);
            node.setIcon(tb);
            nodelist.put(node.getId(), node);
        }
        // 构造树形结构
        List<TreeNode> tnlist = TreeUtil.getNodeList(nodelist);

        baseResponse.setData(tnlist);
        return baseResponse;
    }

    @Override
    public BaseResponse getWjResultDA1(String wjCode, String startTime, String endTime) throws Exception {


        if (StringUtils.isEmpty(startTime)) {
            startTime = "2024-06-20 00:00:00";
        }

        if (StringUtils.isEmpty(endTime)) {
            endTime = DateUtil.format(new Date(),DateUtil.YYYY_MM_DD)+" 23:59:59";
        }

        BaseResponse baseResponse = new BaseResponse();

        String sql = "select o.name deptName,totalPoint/cnt avgPoint,pcnt1,pcnt2,cnt1,cnt2 from (" +
                "   select deptId,count(*) pcnt," +
                "   sum(case when u.type = '规上企业' then 1 else 0 end) pcnt1 ," +
                "   sum(case when u.type = '小微企业' then 1 else 0 end) pcnt2," +
                "   sum(case when r.point is not null then point else 0 end) totalPoint, " +
                "   sum(case when r.point is not null then 1 else 0 end) cnt ," +
                "   sum(case when r.point is not null  and u.type = '规上企业' then 1 else 0 end) cnt1 ," +
                "   sum(case when r.point is not null  and u.type = '小微企业' then 1 else 0 end) cnt2 " +
                "   from app_user u left join " +
                "   (select * from app_wj_result where createtime>=:startTime and createtime<=:endTime) r on r.userId = u.id  group by u.deptId" +
                ") u left join tbl_org o on u.deptId = o.id" +
                " order by o.levelCode asc";
        Map<String, Object> params = new HashMap<>();
        params.put("startTime",startTime);
        params.put("endTime",endTime);
        if (StringUtils.isNotEmpty(wjCode)) {
            sql = "select o.name deptName,totalPoint/cnt avgPoint,pcnt1,pcnt2,cnt1,cnt2 from (" +
                    "   select deptId,count(*) pcnt," +
                    "   sum(case when u.type = '规上企业' then 1 else 0 end) pcnt1 ," +
                    "   sum(case when u.type = '小微企业' then 1 else 0 end) pcnt2," +
                    "   sum(case when r.point is not null then point else 0 end) totalPoint, " +
                    "   sum(case when r.point is not null then 1 else 0 end) cnt ," +
                    "   sum(case when r.point is not null  and u.type = '规上企业' then 1 else 0 end) cnt1 ," +
                    "   sum(case when r.point is not null  and u.type = '小微企业' then 1 else 0 end) cnt2 " +
                    "   from app_user u left join " +
                    "   (select * from app_wj_result where wjCode = :wjCode and createtime>=:startTime and createtime<=:endTime) r on r.userId = u.id  group by u.deptId" +
                    ") u left join tbl_org o on u.deptId = o.id" +
                    " order by o.levelCode asc";
            params.put("wjCode",wjCode);
        }

        List<Map<String, Object>> list = findMapBySql(sql, params);
        baseResponse.setData(list);
        return baseResponse;
    }
    @Override
    public void exportWjResultDA1(String token,String wjCode, String startTime, String endTime,HttpServletResponse response)  {
        try {

            if (StringUtils.isEmpty(startTime)) {
                startTime = "2024-06-20 00:00:00";
            }

            if (StringUtils.isEmpty(endTime)) {
                endTime = DateUtil.format(new Date(), DateUtil.YYYY_MM_DD) + " 23:59:59";
            }

            response.setCharacterEncoding("UTF-8");
            //设置ContentType字段值
            response.setContentType("text/html;charset=utf-8");

            BaseResponse baseResponse = checkEmpToken(token);
            if (baseResponse.getStatus() != 0) {
                response.getWriter().write(new Gson().toJson(baseResponse));
                return;
            }
            String sql = "select o.name deptName,totalPoint/cnt avgPoint,pcnt1,pcnt2,cnt1,cnt2 from (" +
                    "   select deptId,count(*) pcnt," +
                    "   sum(case when u.type = '规上企业' then 1 else 0 end) pcnt1 ," +
                    "   sum(case when u.type = '小微企业' then 1 else 0 end) pcnt2," +
                    "   sum(case when r.point is not null then point else 0 end) totalPoint, " +
                    "   sum(case when r.point is not null then 1 else 0 end) cnt ," +
                    "   sum(case when r.point is not null  and u.type = '规上企业' then 1 else 0 end) cnt1 ," +
                    "   sum(case when r.point is not null  and u.type = '小微企业' then 1 else 0 end) cnt2 " +
                    "   from app_user u left join " +
                    "   (select * from app_wj_result where createtime>=:startTime and createtime<=:endTime) r on r.userId = u.id  group by u.deptId" +
                    ") u left join tbl_org o on u.deptId = o.id" +
                    " order by o.levelCode asc";
            Map<String, Object> params = new HashMap<>();
            params.put("startTime", startTime);
            params.put("endTime", endTime);
            if (StringUtils.isNotEmpty(wjCode)) {
                sql = "select o.name deptName,totalPoint/cnt avgPoint,pcnt1,pcnt2,cnt1,cnt2 from (" +
                        "   select deptId,count(*) pcnt," +
                        "   sum(case when u.type = '规上企业' then 1 else 0 end) pcnt1 ," +
                        "   sum(case when u.type = '小微企业' then 1 else 0 end) pcnt2," +
                        "   sum(case when r.point is not null then point else 0 end) totalPoint, " +
                        "   sum(case when r.point is not null then 1 else 0 end) cnt ," +
                        "   sum(case when r.point is not null  and u.type = '规上企业' then 1 else 0 end) cnt1 ," +
                        "   sum(case when r.point is not null  and u.type = '小微企业' then 1 else 0 end) cnt2 " +
                        "   from app_user u left join " +
                        "   (select * from app_wj_result where wjCode = :wjCode and createtime>=:startTime and createtime<=:endTime) r on r.userId = u.id  group by u.deptId" +
                        ") u left join tbl_org o on u.deptId = o.id" +
                        " order by o.levelCode asc";
                params.put("wjCode", wjCode);
            }

            List<Map<String, Object>> dataList = findMapBySql(sql, params);

            if (dataList == null || dataList.size() == 0) {
                baseResponse.setStatus(1);
                baseResponse.setMessage("无数据！");
                response.getWriter().write(new Gson().toJson(baseResponse));
                return;
            }
            //excel标题
            String[] title = {"单位", "得分", "规上企业（应答）", "小微企业（应答）", "规上企业（实际）", "小微企业（实际）"};
            //excel文件名
            String fileName = "每个单位总体情况统计" + DateUtil.format(new Date(), "yyyyMMddHHmmss") + ".xlsx";
            //sheet名
            String sheetName = "统计";

            String content[][] = new String[dataList.size()][title.length];

            for (int i = 0; i < dataList.size(); i++) {
                Map<String, Object> map = dataList.get(i);

                content[i][0] = map.get("deptName") == null ? "" : map.get("deptName").toString();
                content[i][1] = map.get("avgPoint") == null ? "" : map.get("avgPoint").toString();
                content[i][2] = map.get("pcnt1") == null ? "" : map.get("pcnt1").toString();
                content[i][3] = map.get("pcnt2") == null ? "" : map.get("pcnt2").toString();
                content[i][4] = map.get("cnt1") == null ? "" : map.get("cnt1").toString();
                content[i][5] = map.get("cnt2") == null ? "" : map.get("cnt2").toString();


            }
            SXSSFWorkbook wb = Excel07ExportUtil.getSXSSWorkbook(sheetName, title, content, null);
            DownloadUtils.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        }catch (Exception ex){
            ex.printStackTrace();
        }
    }


    @Override
    public BaseResponse getWjResultDA2(String wjCode, String startTime, String endTime) throws Exception {


        if (StringUtils.isEmpty(startTime)) {
            startTime = "2024-06-20 00:00:00";
        }

        if (StringUtils.isEmpty(endTime)) {
            endTime = DateUtil.format(new Date(),DateUtil.YYYY_MM_DD)+" 23:59:59";
        }

        BaseResponse baseResponse = new BaseResponse();

        String sql = "select o.name deptName,q.name quesText,da.* from (" +
                "   select u.deptId,ri.quesCode," +
                "   sum(case when u.type = '规上企业' then 1 else 0 end) pcnt1, " +
                "   sum(case when u.type = '小微企业' then 1 else 0 end) pcnt2, " +
                "   sum(case when u.type = '规上企业' and ri.answCode='op_my' then 1 else 0 end) mycnt1, " +
                "   sum(case when u.type = '小微企业' and ri.answCode='op_my' then 1 else 0 end) mycnt2, " +
                "   sum(case when u.type = '规上企业' and ri.answCode='op_bjmy' then 1 else 0 end) bjmycnt1, " +
                "   sum(case when u.type = '小微企业' and ri.answCode='op_bjmy' then 1 else 0 end) bjmycnt2, " +
                "   sum(case when u.type = '规上企业' and ri.answCode='op_yb' then 1 else 0 end) ybcnt1, " +
                "   sum(case when u.type = '小微企业' and ri.answCode='op_yb' then 1 else 0 end) ybcnt2, " +
                "   sum(case when u.type = '规上企业' and ri.answCode='op_bmy' then 1 else 0 end) bmycnt1, " +
                "   sum(case when u.type = '小微企业' and ri.answCode='op_bmy' then 1 else 0 end) bmycnt2, " +
                "   sum(case when u.type = '规上企业' and ri.answCode='op_blj' then 1 else 0 end) bljcnt1, " +
                "   sum(case when u.type = '小微企业' and ri.answCode='op_blj' then 1 else 0 end) bljcnt2," +
                "   group_concat(case when ri.answCode='op_bmy' then tag else null end) tags " +
                "   from app_wj_result_item ri left join app_user u on ri.userId = u.id  " +
                "   where ri.createtime>=:startTime and ri.createtime<=:endTime group by u.deptId,ri.quesCode) da " +
                " left join tbl_org o on da.deptId = o.id " +
                " left join tbl_dict q on da.quesCode = q.code " +
                " where q.value is null or q.value<>'1'" +
                " order by o.levelCode asc ,q.levelCode asc";
        Map<String, Object> params = new HashMap<>();
        params.put("startTime",startTime);
        params.put("endTime",endTime);
        if (StringUtils.isNotEmpty(wjCode)) {
            sql = "select o.name deptName,q.name quesText,da.* from (" +
                    "   select u.deptId,ri.quesCode," +
                    "   sum(case when u.type = '规上企业' then 1 else 0 end) pcnt1, " +
                    "   sum(case when u.type = '小微企业' then 1 else 0 end) pcnt2, " +
                    "   sum(case when u.type = '规上企业' and ri.answCode='op_my' then 1 else 0 end) mycnt1, " +
                    "   sum(case when u.type = '小微企业' and ri.answCode='op_my' then 1 else 0 end) mycnt2, " +
                    "   sum(case when u.type = '规上企业' and ri.answCode='op_bjmy' then 1 else 0 end) bjmycnt1, " +
                    "   sum(case when u.type = '小微企业' and ri.answCode='op_bjmy' then 1 else 0 end) bjmycnt2, " +
                    "   sum(case when u.type = '规上企业' and ri.answCode='op_yb' then 1 else 0 end) ybcnt1, " +
                    "   sum(case when u.type = '小微企业' and ri.answCode='op_yb' then 1 else 0 end) ybcnt2, " +
                    "   sum(case when u.type = '规上企业' and ri.answCode='op_bmy' then 1 else 0 end) bmycnt1, " +
                    "   sum(case when u.type = '小微企业' and ri.answCode='op_bmy' then 1 else 0 end) bmycnt2, " +
                    "   sum(case when u.type = '规上企业' and ri.answCode='op_blj' then 1 else 0 end) bljcnt1, " +
                    "   sum(case when u.type = '小微企业' and ri.answCode='op_blj' then 1 else 0 end) bljcnt2," +
                    "   group_concat(case when ri.answCode='op_bmy' then tag else null end) tags " +
                    "   from app_wj_result_item ri left join app_user u on ri.userId = u.id  " +
                    "   where ri.wjCode = :wjCode and ri.createtime>=:startTime and ri.createtime<=:endTime group by u.deptId,ri.quesCode) da " +
                    " left join tbl_org o on da.deptId = o.id " +
                    " left join tbl_dict q on da.quesCode = q.code " +
                    " where q.value is null or q.value<>'1'" +
                    " order by o.levelCode asc ,q.levelCode asc";
            params.put("wjCode",wjCode);
        }

        List<Map<String, Object>> list = findMapBySql(sql, params);
        baseResponse.setData(list);
        return baseResponse;
    }

    @Override
    public BaseResponse getWjResultDAAll2(String wjCode, String startTime, String endTime) throws Exception {

        if (StringUtils.isEmpty(startTime)) {
            startTime = "2024-06-20 00:00:00";
        }

        if (StringUtils.isEmpty(endTime)) {
            endTime = DateUtil.format(new Date(),DateUtil.YYYY_MM_DD)+" 23:59:59";
        }

        BaseResponse baseResponse = new BaseResponse();

        String sql = "select o.name deptName,q.name quesText,da.* from " +
                "(select id,name,levelCode from tbl_org o where o.org_type='1') o cross join " +
                " (select d.id,d.code,d.name,d.levelCode,d.value from tbl_dict d left join tbl_dict p on p.id = d.parent_id where p.code=:wjCode) q" +
                " left join (" +
                "   select u.deptId,ri.quesCode," +
                "   sum(case when u.type = '规上企业' then 1 else 0 end) pcnt1, " +
                "   sum(case when u.type = '小微企业' then 1 else 0 end) pcnt2, " +
                "   sum(case when u.type = '规上企业' and ri.answCode='op_my' then 1 else 0 end) mycnt1, " +
                "   sum(case when u.type = '小微企业' and ri.answCode='op_my' then 1 else 0 end) mycnt2, " +
                "   sum(case when u.type = '规上企业' and ri.answCode='op_bjmy' then 1 else 0 end) bjmycnt1, " +
                "   sum(case when u.type = '小微企业' and ri.answCode='op_bjmy' then 1 else 0 end) bjmycnt2, " +
                "   sum(case when u.type = '规上企业' and ri.answCode='op_yb' then 1 else 0 end) ybcnt1, " +
                "   sum(case when u.type = '小微企业' and ri.answCode='op_yb' then 1 else 0 end) ybcnt2, " +
                "   sum(case when u.type = '规上企业' and ri.answCode='op_bmy' then 1 else 0 end) bmycnt1, " +
                "   sum(case when u.type = '小微企业' and ri.answCode='op_bmy' then 1 else 0 end) bmycnt2, " +
                "   sum(case when u.type = '规上企业' and ri.answCode='op_blj' then 1 else 0 end) bljcnt1, " +
                "   sum(case when u.type = '小微企业' and ri.answCode='op_blj' then 1 else 0 end) bljcnt2," +
                "   group_concat(case when ri.answCode='op_bmy' then tag else null end) tags " +
                "   from app_wj_result_item ri left join app_user u on ri.userId = u.id  " +
                "   where ri.createtime>=:startTime and ri.createtime<=:endTime group by u.deptId,ri.quesCode) da " +
                " on da.deptId = o.id and da.quesCode = q.code " +
                " where q.value is null or q.value<>'1' " +
                " order by o.levelCode asc ,q.levelCode asc";
        Map<String, Object> params = new HashMap<>();
        params.put("startTime",startTime);
        params.put("endTime",endTime);
        if (StringUtils.isNotEmpty(wjCode)) {
            sql = "select o.name deptName,q.name quesText,da.* from " +
                    "(select id,name,levelCode from tbl_org o where o.org_type='1') o cross join " +
                    " (select d.id,d.code,d.name,d.levelCode,d.value from tbl_dict d left join tbl_dict p on p.id = d.parent_id where p.code=:wjCode) q" +
                    " left join (" +
                    "   select u.deptId,ri.quesCode," +
                    "   sum(case when u.type = '规上企业' then 1 else 0 end) pcnt1, " +
                    "   sum(case when u.type = '小微企业' then 1 else 0 end) pcnt2, " +
                    "   sum(case when u.type = '规上企业' and ri.answCode='op_my' then 1 else 0 end) mycnt1, " +
                    "   sum(case when u.type = '小微企业' and ri.answCode='op_my' then 1 else 0 end) mycnt2, " +
                    "   sum(case when u.type = '规上企业' and ri.answCode='op_bjmy' then 1 else 0 end) bjmycnt1, " +
                    "   sum(case when u.type = '小微企业' and ri.answCode='op_bjmy' then 1 else 0 end) bjmycnt2, " +
                    "   sum(case when u.type = '规上企业' and ri.answCode='op_yb' then 1 else 0 end) ybcnt1, " +
                    "   sum(case when u.type = '小微企业' and ri.answCode='op_yb' then 1 else 0 end) ybcnt2, " +
                    "   sum(case when u.type = '规上企业' and ri.answCode='op_bmy' then 1 else 0 end) bmycnt1, " +
                    "   sum(case when u.type = '小微企业' and ri.answCode='op_bmy' then 1 else 0 end) bmycnt2, " +
                    "   sum(case when u.type = '规上企业' and ri.answCode='op_blj' then 1 else 0 end) bljcnt1, " +
                    "   sum(case when u.type = '小微企业' and ri.answCode='op_blj' then 1 else 0 end) bljcnt2," +
                    "   group_concat(case when ri.answCode='op_bmy' then tag else null end) tags " +
                    "   from app_wj_result_item ri left join app_user u on ri.userId = u.id  " +
                    "   where ri.wjCode = :wjCode and ri.createtime>=:startTime and ri.createtime<=:endTime group by u.deptId,ri.quesCode) da " +
                    " on da.deptId = o.id and da.quesCode = q.code " +
                    " where q.value is null or q.value<>'1' " +
                    " order by o.levelCode asc ,q.levelCode asc";
            params.put("wjCode",wjCode);
        }

        List<Map<String, Object>> list = findMapBySql(sql, params);
        baseResponse.setData(list);
        return baseResponse;
    }

    @Override
    public void exportWjResultDA2(String token, String wjCode, String startTime, String endTime, HttpServletResponse response) {
        try {


            if (StringUtils.isEmpty(startTime)) {
                startTime = "2024-06-20 00:00:00";
            }

            if (StringUtils.isEmpty(endTime)) {
                endTime = DateUtil.format(new Date(), DateUtil.YYYY_MM_DD) + " 23:59:59";
            }

            response.setCharacterEncoding("UTF-8");
            //设置ContentType字段值
            response.setContentType("text/html;charset=utf-8");

            BaseResponse baseResponse = checkEmpToken(token);
            if (baseResponse.getStatus() != 0) {
                response.getWriter().write(new Gson().toJson(baseResponse));
                return;
            }
            String sql = "select o.name deptName,q.name quesText,da.* from (" +
                    "   select u.deptId,ri.quesCode," +
                    "   sum(case when u.type = '规上企业' then 1 else 0 end) pcnt1, " +
                    "   sum(case when u.type = '小微企业' then 1 else 0 end) pcnt2, " +
                    "   sum(case when u.type = '规上企业' and ri.answCode='op_my' then 1 else 0 end) mycnt1, " +
                    "   sum(case when u.type = '小微企业' and ri.answCode='op_my' then 1 else 0 end) mycnt2, " +
                    "   sum(case when u.type = '规上企业' and ri.answCode='op_bjmy' then 1 else 0 end) bjmycnt1, " +
                    "   sum(case when u.type = '小微企业' and ri.answCode='op_bjmy' then 1 else 0 end) bjmycnt2, " +
                    "   sum(case when u.type = '规上企业' and ri.answCode='op_yb' then 1 else 0 end) ybcnt1, " +
                    "   sum(case when u.type = '小微企业' and ri.answCode='op_yb' then 1 else 0 end) ybcnt2, " +
                    "   sum(case when u.type = '规上企业' and ri.answCode='op_bmy' then 1 else 0 end) bmycnt1, " +
                    "   sum(case when u.type = '小微企业' and ri.answCode='op_bmy' then 1 else 0 end) bmycnt2, " +
                    "   sum(case when u.type = '规上企业' and ri.answCode='op_blj' then 1 else 0 end) bljcnt1, " +
                    "   sum(case when u.type = '小微企业' and ri.answCode='op_blj' then 1 else 0 end) bljcnt2," +
                    "   group_concat(case when ri.answCode='op_bmy' then tag else null end) tags " +
                    "   from app_wj_result_item ri left join app_user u on ri.userId = u.id  " +
                    "   where ri.createtime>=:startTime and ri.createtime<=:endTime group by u.deptId,ri.quesCode) da " +
                    " left join tbl_org o on da.deptId = o.id " +
                    " left join tbl_dict q on da.quesCode = q.code " +
                    " where q.value is null or q.value<>'1' " +
                    " order by o.levelCode asc ,q.levelCode asc";

            Map<String, Object> params = new HashMap<>();
            params.put("startTime", startTime);
            params.put("endTime", endTime);
            if (StringUtils.isNotEmpty(wjCode)) {
                sql = "select o.name deptName,q.name quesText,da.* from (" +
                        "   select u.deptId,ri.quesCode," +
                        "   sum(case when u.type = '规上企业' then 1 else 0 end) pcnt1, " +
                        "   sum(case when u.type = '小微企业' then 1 else 0 end) pcnt2, " +
                        "   sum(case when u.type = '规上企业' and ri.answCode='op_my' then 1 else 0 end) mycnt1, " +
                        "   sum(case when u.type = '小微企业' and ri.answCode='op_my' then 1 else 0 end) mycnt2, " +
                        "   sum(case when u.type = '规上企业' and ri.answCode='op_bjmy' then 1 else 0 end) bjmycnt1, " +
                        "   sum(case when u.type = '小微企业' and ri.answCode='op_bjmy' then 1 else 0 end) bjmycnt2, " +
                        "   sum(case when u.type = '规上企业' and ri.answCode='op_yb' then 1 else 0 end) ybcnt1, " +
                        "   sum(case when u.type = '小微企业' and ri.answCode='op_yb' then 1 else 0 end) ybcnt2, " +
                        "   sum(case when u.type = '规上企业' and ri.answCode='op_bmy' then 1 else 0 end) bmycnt1, " +
                        "   sum(case when u.type = '小微企业' and ri.answCode='op_bmy' then 1 else 0 end) bmycnt2, " +
                        "   sum(case when u.type = '规上企业' and ri.answCode='op_blj' then 1 else 0 end) bljcnt1, " +
                        "   sum(case when u.type = '小微企业' and ri.answCode='op_blj' then 1 else 0 end) bljcnt2," +
                        "   group_concat(case when ri.answCode='op_bmy' then tag else null end) tags " +
                        "   from app_wj_result_item ri left join app_user u on ri.userId = u.id  " +
                        "   where ri.wjCode = :wjCode and ri.createtime>=:startTime and ri.createtime<=:endTime group by u.deptId,ri.quesCode) da " +
                        " left join tbl_org o on da.deptId = o.id " +
                        " left join tbl_dict q on da.quesCode = q.code " +
                        " where q.value is null or q.value<>'1' " +
                        " order by o.levelCode asc ,q.levelCode asc";
                params.put("wjCode", wjCode);
            }

            List<Map<String, Object>> dataList = findMapBySql(sql, params);

            if (dataList == null || dataList.size() == 0) {
                baseResponse.setStatus(1);
                baseResponse.setMessage("无数据！");
                response.getWriter().write(new Gson().toJson(baseResponse));
                return;
            }
            //excel标题
            String[] title = {"单位", "序号", "满意（规上）", "满意（小微）", "比较满意（规上）", "比较满意（小微）", "一般（规上）", "一般（小微）", "不满意（规上）", "不满意（小微）", "不了解（规上）", "不了解（小微）","备注（不满意对应的事项或单位、人员）"};
            //excel文件名
            String fileName = "每个单位分项情况统计" + DateUtil.format(new Date(), "yyyyMMddHHmmss") + ".xlsx";
            //sheet名
            String sheetName = "统计";

            String content[][] = new String[dataList.size()][title.length];
            String temp = "";
            int index = 0;
            for (int i = 0; i < dataList.size(); i++) {
                Map<String, Object> map = dataList.get(i);
                String deptName = map.get("deptName") == null ? "" : map.get("deptName").toString();
                if (temp.equals(deptName)) {
                    index ++;
                }else {
                    index = 1;
                    temp = deptName;
                }
                content[i][0] = deptName;
                content[i][1] = String.format("%02d",index);
                content[i][2] = map.get("mycnt1") == null ? "" : map.get("mycnt1").toString();
                content[i][3] = map.get("mycnt2") == null ? "" : map.get("mycnt2").toString();
                content[i][4] = map.get("bjmycnt1") == null ? "" : map.get("bjmycnt1").toString();
                content[i][5] = map.get("bjmycnt2") == null ? "" : map.get("bjmycnt2").toString();
                content[i][6] = map.get("ybcnt1") == null ? "" : map.get("ybcnt1").toString();
                content[i][7] = map.get("ybcnt2") == null ? "" : map.get("ybcnt2").toString();
                content[i][8] = map.get("bmycnt1") == null ? "" : map.get("bmycnt1").toString();
                content[i][9] = map.get("bmycnt2") == null ? "" : map.get("bmycnt2").toString();
                content[i][10] = map.get("bljcnt1") == null ? "" : map.get("bljcnt1").toString();
                content[i][11] = map.get("bljcnt2") == null ? "" : map.get("bljcnt2").toString();
                content[i][12] = map.get("tags") == null ? "" : map.get("tags").toString();


            }
            SXSSFWorkbook wb = Excel07ExportUtil.getSXSSWorkbook(sheetName, title, content, null);
            DownloadUtils.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        }catch (Exception ex){
            ex.printStackTrace();
        }
    }

    @Override
    public void exportWjResultDAAll2(String token, String wjCode, String startTime, String endTime, HttpServletResponse response) {
        try {


            if (StringUtils.isEmpty(startTime)) {
                startTime = "2024-06-20 00:00:00";
            }

            if (StringUtils.isEmpty(endTime)) {
                endTime = DateUtil.format(new Date(), DateUtil.YYYY_MM_DD) + " 23:59:59";
            }

            response.setCharacterEncoding("UTF-8");
            //设置ContentType字段值
            response.setContentType("text/html;charset=utf-8");

            BaseResponse baseResponse = checkEmpToken(token);
            if (baseResponse.getStatus() != 0) {
                response.getWriter().write(new Gson().toJson(baseResponse));
                return;
            }
            String sql = "select o.name deptName,q.name quesText,da.* from " +
                    "(select id,name,levelCode from tbl_org o where o.org_type='1') o cross join " +
                    " (select d.id,d.code,d.name,d.levelCode from tbl_dict d left join tbl_dict p on p.id = d.parent_id where p.code=:wjCode) q" +
                    " left join (" +
                    "   select u.deptId,ri.quesCode," +
                    "   sum(case when u.type = '规上企业' then 1 else 0 end) pcnt1, " +
                    "   sum(case when u.type = '小微企业' then 1 else 0 end) pcnt2, " +
                    "   sum(case when u.type = '规上企业' and ri.answCode='op_my' then 1 else 0 end) mycnt1, " +
                    "   sum(case when u.type = '小微企业' and ri.answCode='op_my' then 1 else 0 end) mycnt2, " +
                    "   sum(case when u.type = '规上企业' and ri.answCode='op_bjmy' then 1 else 0 end) bjmycnt1, " +
                    "   sum(case when u.type = '小微企业' and ri.answCode='op_bjmy' then 1 else 0 end) bjmycnt2, " +
                    "   sum(case when u.type = '规上企业' and ri.answCode='op_yb' then 1 else 0 end) ybcnt1, " +
                    "   sum(case when u.type = '小微企业' and ri.answCode='op_yb' then 1 else 0 end) ybcnt2, " +
                    "   sum(case when u.type = '规上企业' and ri.answCode='op_bmy' then 1 else 0 end) bmycnt1, " +
                    "   sum(case when u.type = '小微企业' and ri.answCode='op_bmy' then 1 else 0 end) bmycnt2, " +
                    "   sum(case when u.type = '规上企业' and ri.answCode='op_blj' then 1 else 0 end) bljcnt1, " +
                    "   sum(case when u.type = '小微企业' and ri.answCode='op_blj' then 1 else 0 end) bljcnt2," +
                    "   group_concat(case when ri.answCode='op_bmy' then tag else null end) tags " +
                    "   from app_wj_result_item ri left join app_user u on ri.userId = u.id  " +
                    "   where ri.createtime>=:startTime and ri.createtime<=:endTime group by u.deptId,ri.quesCode) da " +
                    " on da.deptId = o.id and da.quesCode = q.code " +
                    " where q.value is null or q.value<>'1' " +
                    " order by o.levelCode asc ,q.levelCode asc";

            Map<String, Object> params = new HashMap<>();
            params.put("startTime", startTime);
            params.put("endTime", endTime);
            if (StringUtils.isNotEmpty(wjCode)) {
                sql = "select o.name deptName,q.name quesText,da.* from " +
                        "(select id,name,levelCode from tbl_org o where o.org_type='1') o cross join " +
                        " (select d.id,d.code,d.name,d.levelCode from tbl_dict d left join tbl_dict p on p.id = d.parent_id where p.code=:wjCode) q" +
                        " left join (" +
                        "   select u.deptId,ri.quesCode," +
                        "   sum(case when u.type = '规上企业' then 1 else 0 end) pcnt1, " +
                        "   sum(case when u.type = '小微企业' then 1 else 0 end) pcnt2, " +
                        "   sum(case when u.type = '规上企业' and ri.answCode='op_my' then 1 else 0 end) mycnt1, " +
                        "   sum(case when u.type = '小微企业' and ri.answCode='op_my' then 1 else 0 end) mycnt2, " +
                        "   sum(case when u.type = '规上企业' and ri.answCode='op_bjmy' then 1 else 0 end) bjmycnt1, " +
                        "   sum(case when u.type = '小微企业' and ri.answCode='op_bjmy' then 1 else 0 end) bjmycnt2, " +
                        "   sum(case when u.type = '规上企业' and ri.answCode='op_yb' then 1 else 0 end) ybcnt1, " +
                        "   sum(case when u.type = '小微企业' and ri.answCode='op_yb' then 1 else 0 end) ybcnt2, " +
                        "   sum(case when u.type = '规上企业' and ri.answCode='op_bmy' then 1 else 0 end) bmycnt1, " +
                        "   sum(case when u.type = '小微企业' and ri.answCode='op_bmy' then 1 else 0 end) bmycnt2, " +
                        "   sum(case when u.type = '规上企业' and ri.answCode='op_blj' then 1 else 0 end) bljcnt1, " +
                        "   sum(case when u.type = '小微企业' and ri.answCode='op_blj' then 1 else 0 end) bljcnt2," +
                        "   group_concat(case when ri.answCode='op_bmy' then tag else null end) tags " +
                        "   from app_wj_result_item ri left join app_user u on ri.userId = u.id  " +
                        "   where ri.wjCode = :wjCode and ri.createtime>=:startTime and ri.createtime<=:endTime group by u.deptId,ri.quesCode) da " +
                        " on da.deptId = o.id and da.quesCode = q.code " +
                        " where q.value is null or q.value<>'1' " +
                        " order by o.levelCode asc ,q.levelCode asc";
                params.put("wjCode", wjCode);
            }

            List<Map<String, Object>> dataList = findMapBySql(sql, params);

            if (dataList == null || dataList.size() == 0) {
                baseResponse.setStatus(1);
                baseResponse.setMessage("无数据！");
                response.getWriter().write(new Gson().toJson(baseResponse));
                return;
            }
            //excel标题
            String[] title = {"单位", "序号", "满意（规上）", "满意（小微）", "比较满意（规上）", "比较满意（小微）", "一般（规上）", "一般（小微）", "不满意（规上）", "不满意（小微）", "不了解（规上）", "不了解（小微）","备注（不满意对应的事项或单位、人员）"};
            //excel文件名
            String fileName = "每个单位分项情况统计" + DateUtil.format(new Date(), "yyyyMMddHHmmss") + ".xlsx";
            //sheet名
            String sheetName = "统计";

            String content[][] = new String[dataList.size()][title.length];
            String temp = "";
            int index = 0;
            for (int i = 0; i < dataList.size(); i++) {
                Map<String, Object> map = dataList.get(i);
                String deptName = map.get("deptName") == null ? "" : map.get("deptName").toString();
                if (temp.equals(deptName)) {
                    index ++;
                }else {
                    index = 1;
                    temp = deptName;
                }
                content[i][0] = deptName;
                content[i][1] = String.format("%02d",index);
                content[i][2] = map.get("mycnt1") == null ? "" : map.get("mycnt1").toString();
                content[i][3] = map.get("mycnt2") == null ? "" : map.get("mycnt2").toString();
                content[i][4] = map.get("bjmycnt1") == null ? "" : map.get("bjmycnt1").toString();
                content[i][5] = map.get("bjmycnt2") == null ? "" : map.get("bjmycnt2").toString();
                content[i][6] = map.get("ybcnt1") == null ? "" : map.get("ybcnt1").toString();
                content[i][7] = map.get("ybcnt2") == null ? "" : map.get("ybcnt2").toString();
                content[i][8] = map.get("bmycnt1") == null ? "" : map.get("bmycnt1").toString();
                content[i][9] = map.get("bmycnt2") == null ? "" : map.get("bmycnt2").toString();
                content[i][10] = map.get("bljcnt1") == null ? "" : map.get("bljcnt1").toString();
                content[i][11] = map.get("bljcnt2") == null ? "" : map.get("bljcnt2").toString();
                content[i][12] = map.get("tags") == null ? "" : map.get("tags").toString();


            }
            SXSSFWorkbook wb = Excel07ExportUtil.getSXSSWorkbook(sheetName, title, content, null);
            DownloadUtils.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        }catch (Exception ex){
            ex.printStackTrace();
        }
    }
    @Override
    public BaseResponse getWjResultDA3(String wjCode, String startTime, String endTime) throws Exception {


        if (StringUtils.isEmpty(startTime)) {
            startTime = "2024-06-20 00:00:00";
        }

        if (StringUtils.isEmpty(endTime)) {
            endTime = DateUtil.format(new Date(),DateUtil.YYYY_MM_DD)+" 23:59:59";
        }

        BaseResponse baseResponse = new BaseResponse();

        String sql = "select o.name deptName,da.* from " +
                "(select id,name,levelCode from tbl_org o where o.org_type='1') o" +
                " left join (" +
                "   select u.deptId,group_concat(tag separator '---') tags " +
                "   from app_wj_result_item ri " +
                "   left join app_user u on ri.userId = u.id" +
                "   left join tbl_dict q on ri.quesCode = q.code " +
                "   where ri.createtime>=:startTime and ri.createtime<=:endTime and q.value='1' group by u.deptId) da " +
                " on da.deptId = o.id order by o.levelCode asc";
        Map<String, Object> params = new HashMap<>();
        params.put("startTime",startTime);
        params.put("endTime",endTime);
        if (StringUtils.isNotEmpty(wjCode)) {
            sql = "select o.name deptName,da.* from " +
                    "(select id,name,levelCode from tbl_org o where o.org_type='1') o" +
                    " left join (" +
                    "   select u.deptId,group_concat(tag separator '---') tags " +
                    "   from app_wj_result_item ri " +
                    "   left join app_user u on ri.userId = u.id" +
                    "   left join tbl_dict q on ri.quesCode = q.code " +
                    "   where ri.wjCode = :wjCode and ri.createtime>=:startTime and ri.createtime<=:endTime and q.value='1' group by u.deptId) da " +
                    " on da.deptId = o.id order by o.levelCode asc";
            params.put("wjCode",wjCode);
        }

        List<Map<String, Object>> list = findMapBySql(sql, params);
        baseResponse.setData(list);
        return baseResponse;
    }

    @Override
    public BaseResponse getWjResultFinalTags(String wjCode, String startTime, String endTime) throws Exception {

        if (StringUtils.isEmpty(startTime)) {
            startTime = "2024-06-20 00:00:00";
        }

        if (StringUtils.isEmpty(endTime)) {
            endTime = DateUtil.format(new Date(),DateUtil.YYYY_MM_DD)+" 23:59:59";
        }

        BaseResponse baseResponse = new BaseResponse();

        String sql = "select o.name deptName,ri.tag tags " +
                " from app_wj_result_item ri " +
                " left join app_user u on ri.userId = u.id " +
                " left join tbl_dict q on ri.quesCode = q.code " +
                " left join (select id,name,levelCode from tbl_org o where o.org_type='1') o on o.id = u.deptId " +
                " where  ri.createtime>=:startTime and ri.createtime<=:endTime and q.value='1' " +
                " order by o.levelCode asc";
        Map<String, Object> params = new HashMap<>();
        params.put("startTime",startTime);
        params.put("endTime",endTime);
        if (StringUtils.isNotEmpty(wjCode)) {

            sql = "select o.name deptName,ri.tag tags " +
                    " from app_wj_result_item ri " +
                    " left join app_user u on ri.userId = u.id " +
                    " left join tbl_dict q on ri.quesCode = q.code " +
                    " left join (select id,name,levelCode from tbl_org o where o.org_type='1') o on o.id = u.deptId " +
                    " where ri.wjCode = :wjCode and ri.createtime>=:startTime and ri.createtime<=:endTime and q.value='1' " +
                    " order by o.levelCode asc";
            params.put("wjCode",wjCode);
        }

        List<Map<String, Object>> list = findMapBySql(sql, params);
        baseResponse.setData(list);
        return baseResponse;
    }


    @Override
    public BaseResponse getWjResultFinalTags241012(String wjCode, String startTime, String endTime,String sortType) throws Exception {

        if (StringUtils.isEmpty(startTime)) {
            startTime = "2024-06-20 00:00:00";
        }

        if (StringUtils.isEmpty(endTime)) {
            endTime = DateUtil.format(new Date(),DateUtil.YYYY_MM_DD)+" 23:59:59";
        }

        BaseResponse baseResponse = new BaseResponse();

        String sql = "select ri.createtime,substr(q.levelCode,length(q.levelCode)-1,2) qnum,ri.quesText,ri.tag tags " +
                " from app_wj_result_item ri " +
                " left join app_user u on ri.userId = u.id " +
                " left join tbl_dict q on ri.quesCode = q.code " +
                " where  ri.createtime>=:startTime and ri.createtime<=:endTime and q.value='2' and ri.tag is not null  and ri.tag<>'' and ri.tag<>'无'";
        Map<String, Object> params = new HashMap<>();
        params.put("startTime",startTime);
        params.put("endTime",endTime);
        if (StringUtils.isNotEmpty(wjCode)) {

            sql = "select ri.createtime,substr(q.levelCode,length(q.levelCode)-1,2) qnum,ri.quesText,ri.tag tags " +
                    " from app_wj_result_item ri " +
                    " left join app_user u on ri.userId = u.id " +
                    " left join tbl_dict q on ri.quesCode = q.code " +
                    " where ri.wjCode = :wjCode and ri.createtime>=:startTime and ri.createtime<=:endTime and q.value='2' and ri.tag is not null  and ri.tag<>'' and ri.tag<>'无'";
            params.put("wjCode",wjCode);
        }


        if ("1".equals(sortType)) {
            sql += " order by q.levelCode asc,ri.id asc";
        }else {
            sql += " order by q.levelCode asc,ri.id desc";
        }

        List<Map<String, Object>> list = findMapBySql(sql, params);
        baseResponse.setData(list);
        return baseResponse;
    }

    @Override
    public void exportWjResultDA3(String token, String wjCode, String startTime, String endTime, HttpServletResponse response) {
        try {

            if (StringUtils.isEmpty(startTime)) {
                startTime = "2024-06-20 00:00:00";
            }

            if (StringUtils.isEmpty(endTime)) {
                endTime = DateUtil.format(new Date(), DateUtil.YYYY_MM_DD) + " 23:59:59";
            }

            response.setCharacterEncoding("UTF-8");
            //设置ContentType字段值
            response.setContentType("text/html;charset=utf-8");

            BaseResponse baseResponse = checkEmpToken(token);
            if (baseResponse.getStatus() != 0) {
                response.getWriter().write(new Gson().toJson(baseResponse));
                return;
            }
            String sql = "select o.name deptName,da.* from " +
                    "(select id,name,levelCode from tbl_org o where o.org_type='1') o" +
                    " left join (" +
                    "   select u.deptId,group_concat(tag separator '---') tags " +
                    "   from app_wj_result_item ri " +
                    "   left join app_user u on ri.userId = u.id" +
                    "   left join tbl_dict q on ri.quesCode = q.code " +
                    "   where ri.createtime>=:startTime and ri.createtime<=:endTime and q.value='1' group by u.deptId) da " +
                    " on da.deptId = o.id order by o.levelCode asc";

            Map<String, Object> params = new HashMap<>();
            params.put("startTime", startTime);
            params.put("endTime", endTime);

            if (StringUtils.isNotEmpty(wjCode)) {
                sql = "select o.name deptName,da.* from " +
                        "(select id,name,levelCode from tbl_org o where o.org_type='1') o" +
                        " left join (" +
                        "   select u.deptId,group_concat(tag separator '---') tags " +
                        "   from app_wj_result_item ri " +
                        "   left join app_user u on ri.userId = u.id" +
                        "   left join tbl_dict q on ri.quesCode = q.code " +
                        "   where ri.wjCode = :wjCode and ri.createtime>=:startTime and ri.createtime<=:endTime and q.value='1' group by u.deptId) da " +
                        " on da.deptId = o.id order by o.levelCode asc";
                params.put("wjCode", wjCode);
            }

            List<Map<String, Object>> dataList = findMapBySql(sql, params);

            if (dataList == null || dataList.size() == 0) {
                baseResponse.setStatus(1);
                baseResponse.setMessage("无数据！");
                response.getWriter().write(new Gson().toJson(baseResponse));
                return;
            }
            //excel标题
            String[] title = {"单位", "意见建议1", "意见建议", "意见建议", "意见建议", "意见建议", "意见建议", "一般（小微）", "不满意（规上）", "不满意（小微）", "不了解（规上）", "不了解（小微）","备注（不满意对应的事项或单位、人员）"};
            //excel文件名
            String fileName = "每个单位分项情况统计" + DateUtil.format(new Date(), "yyyyMMddHHmmss") + ".xlsx";
            //sheet名
            String sheetName = "统计";

            String content[][] = new String[dataList.size()][title.length];
            String temp = "";
            int index = 0;
            for (int i = 0; i < dataList.size(); i++) {
                Map<String, Object> map = dataList.get(i);
                String deptName = map.get("deptName") == null ? "" : map.get("deptName").toString();
                if (temp.equals(deptName)) {
                    index ++;
                }else {
                    index = 1;
                    temp = deptName;
                }
                content[i][0] = deptName;
                content[i][1] = String.format("%02d",index);
                content[i][2] = map.get("mycnt1") == null ? "" : map.get("mycnt1").toString();
                content[i][3] = map.get("mycnt2") == null ? "" : map.get("mycnt2").toString();
                content[i][4] = map.get("bjmycnt1") == null ? "" : map.get("bjmycnt1").toString();
                content[i][5] = map.get("bjmycnt2") == null ? "" : map.get("bjmycnt2").toString();
                content[i][6] = map.get("ybcnt1") == null ? "" : map.get("ybcnt1").toString();
                content[i][7] = map.get("ybcnt2") == null ? "" : map.get("ybcnt2").toString();
                content[i][8] = map.get("bmycnt1") == null ? "" : map.get("bmycnt1").toString();
                content[i][9] = map.get("bmycnt2") == null ? "" : map.get("bmycnt2").toString();
                content[i][10] = map.get("bljcnt1") == null ? "" : map.get("bljcnt1").toString();
                content[i][11] = map.get("bljcnt2") == null ? "" : map.get("bljcnt2").toString();
                content[i][12] = map.get("tags") == null ? "" : map.get("tags").toString();


            }
            SXSSFWorkbook wb = Excel07ExportUtil.getSXSSWorkbook(sheetName, title, content, null);
            DownloadUtils.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        }catch (Exception ex){
            ex.printStackTrace();
        }
    }


    @Override
    public BaseResponse getWjResultDAByOptionForAll(String wjCode, String startTime, String endTime) throws Exception {

        if (StringUtils.isEmpty(startTime)) {
            startTime = "2024-09-20 00:00:00";
        }

        if (StringUtils.isEmpty(endTime)) {
            endTime = DateUtil.format(new Date(),DateUtil.YYYY_MM_DD)+" 23:59:59";
        }

        BaseResponse baseResponse = new BaseResponse();

        if (StringUtils.isEmpty(wjCode)) {
            baseResponse.setStatus(1);
            baseResponse.setMessage("参数缺失");
            return baseResponse;
        }

        String sql = "select substr(q.prtLevelCode,length(q.prtLevelCode)-1) prtSeq,q.prtName,q.dictCode,q.dictValue,q.dictName,sum(case when ru.id is not null then 1 else 0 end) cnt from " +
                "(select p.name prtName,p.code prtCode,p.levelCode prtLevelCode,d.id dictId,d.code dictCode,d.name dictName,d.levelCode dictLevelCode,d.value dictValue " +
                "   from tbl_dict d left join tbl_dict p on p.id = d.parent_id left join tbl_dict gp on gp.id = p.parent_id where gp.code=:wjCode " +
                ") q " +
                "left join " +
                "(select ri.* from app_wj_result_item ri where ri.wjCode=:wjCode and ri.createtime>=:startTime and ri.createtime<=:endTime) ru on LOCATE(q.dictCode,ru.answCode)>0 " +
                "group by q.dictCode " +
                " order by q.prtLevelCode asc,q.dictLevelCode asc";
        Map<String, Object> params = new HashMap<>();
        params.put("startTime",startTime);
        params.put("endTime",endTime);
        params.put("wjCode",wjCode);

        List<Map<String, Object>> list = findMapBySql(sql, params);
        baseResponse.setData(list);
        return baseResponse;
    }

    @Override
    public void exportWjResultDAByOptionForAll(String token, String wjCode, String startTime, String endTime, HttpServletResponse response) {
        try {

            if (StringUtils.isEmpty(startTime)) {
                startTime = "2024-06-20 00:00:00";
            }

            if (StringUtils.isEmpty(endTime)) {
                endTime = DateUtil.format(new Date(), DateUtil.YYYY_MM_DD) + " 23:59:59";
            }

            response.setCharacterEncoding("UTF-8");
            //设置ContentType字段值
            response.setContentType("text/html;charset=utf-8");

            BaseResponse baseResponse = checkEmpToken(token);
            if (baseResponse.getStatus() != 0) {
                response.getWriter().write(new Gson().toJson(baseResponse));
                return;
            }

            if (StringUtils.isEmpty(wjCode)) {
                baseResponse.setStatus(1);
                baseResponse.setMessage("参数缺失");
                response.getWriter().write(new Gson().toJson(baseResponse));
            }
            String sql = "select substr(q.prtLevelCode,length(q.prtLevelCode)-1) prtSeq,q.prtName,q.dictCode,q.dictValue,q.dictName,sum(case when ru.id is not null then 1 else 0 end) cnt from " +
                    "(select p.name prtName,p.code prtCode,p.levelCode prtLevelCode,d.id dictId,d.code dictCode,d.name dictName,d.levelCode dictLevelCode,d.value dictValue " +
                    "   from tbl_dict d left join tbl_dict p on p.id = d.parent_id left join tbl_dict gp on gp.id = p.parent_id where gp.code=:wjCode " +
                    ") q " +
                    "left join " +
                    "(select ri.* from app_wj_result_item ri where ri.wjCode=:wjCode and ri.createtime>=:startTime and ri.createtime<=:endTime) ru on LOCATE(q.dictCode,ru.answCode)>0 " +
                    " group by q.dictCode " +
                    " order by q.prtLevelCode asc,q.dictLevelCode asc";
            Map<String, Object> params = new HashMap<>();
            params.put("startTime",startTime);
            params.put("endTime",endTime);
            params.put("wjCode",wjCode);

            List<Map<String, Object>> dataList = findMapBySql(sql, params);

            if (dataList == null || dataList.size() == 0) {
                baseResponse.setStatus(1);
                baseResponse.setMessage("无数据！");
                response.getWriter().write(new Gson().toJson(baseResponse));
                return;
            }
            //excel标题
            String[] title = {"序号","题目", "选项序号", "选项", "数量"};
            //excel文件名
            String fileName = "全部统计" + DateUtil.format(new Date(), "yyyyMMddHHmmss") + ".xlsx";
            //sheet名
            String sheetName = "统计";

            String content[][] = new String[dataList.size()][title.length];

            for (int i = 0; i < dataList.size(); i++) {
                Map<String, Object> map = dataList.get(i);
                content[i][0] = map.get("prtSeq") == null ? "" : map.get("prtSeq").toString();
                content[i][1] = map.get("prtName") == null ? "" : map.get("prtName").toString();
                content[i][2] = map.get("dictValue") == null ? "" : map.get("dictValue").toString();
                content[i][3] = map.get("dictName") == null ? "" : map.get("dictName").toString();
                content[i][4] = map.get("cnt") == null ? "" : map.get("cnt").toString();

            }
            SXSSFWorkbook wb = Excel07ExportUtil.getSXSSWorkbook(sheetName, title, content, null);
            DownloadUtils.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        }catch (Exception ex){
            ex.printStackTrace();
        }
    }

    @Override
    public BaseResponse getWjResultDAByOptionAndDept(String wjCode, String startTime, String endTime) throws Exception {
        if (StringUtils.isEmpty(startTime)) {
            startTime = "2024-09-20 00:00:00";
        }

        if (StringUtils.isEmpty(endTime)) {
            endTime = DateUtil.format(new Date(),DateUtil.YYYY_MM_DD)+" 23:59:59";
        }

        BaseResponse baseResponse = new BaseResponse();

        if (StringUtils.isEmpty(wjCode)) {
            baseResponse.setStatus(1);
            baseResponse.setMessage("参数缺失");
            return baseResponse;
        }

        String sql = "select o.deptName,substr(q.prtLevelCode,length(q.prtLevelCode)-1) prtSeq,q.prtName,q.dictCode,q.dictValue,q.dictName,sum(case when ru.id is not null then 1 else 0 end) cnt from " +
                "(select id ,name deptName,levelCode deptLevelCode from tbl_org o where o.org_type='1') o cross join " +
                "(select p.name prtName,p.code prtCode,p.levelCode prtLevelCode,d.id dictId,d.code dictCode,d.name dictName,d.levelCode dictLevelCode,d.value dictValue " +
                "   from tbl_dict d left join tbl_dict p on p.id = d.parent_id left join tbl_dict gp on gp.id = p.parent_id where gp.code=:wjCode" +
                ") q " +
                "left join " +
                "(select ri.*,u.mobile,u.deptId from app_wj_result_item ri left join app_user u on ri.userId = u.id where ri.wjCode=:wjCode and ri.createtime>=:startTime and ri.createtime<=:endTime) ru on LOCATE(q.dictCode,ru.answCode)>0 and ru.deptId = o.id " +
                "group by o.id,q.dictCode " +
                " order by o.deptLevelCode asc,q.prtLevelCode asc,q.dictLevelCode asc";
        Map<String, Object> params = new HashMap<>();
        params.put("startTime",startTime);
        params.put("endTime",endTime);
        params.put("wjCode",wjCode);

        List<Map<String, Object>> list = findMapBySql(sql, params);
        baseResponse.setData(list);
        return baseResponse;
    }



    @Override
    public void exportWjResultDAByOptionAndDept(String token, String wjCode, String startTime, String endTime, HttpServletResponse response) {
        try {

            if (StringUtils.isEmpty(startTime)) {
                startTime = "2024-09-20 00:00:00";
            }

            if (StringUtils.isEmpty(endTime)) {
                endTime = DateUtil.format(new Date(), DateUtil.YYYY_MM_DD) + " 23:59:59";
            }

            response.setCharacterEncoding("UTF-8");
            //设置ContentType字段值
            response.setContentType("text/html;charset=utf-8");

            BaseResponse baseResponse = checkEmpToken(token);
            if (baseResponse.getStatus() != 0) {
                response.getWriter().write(new Gson().toJson(baseResponse));
                return;
            }

            if (StringUtils.isEmpty(wjCode)) {
                baseResponse.setStatus(1);
                baseResponse.setMessage("参数缺失");
                response.getWriter().write(new Gson().toJson(baseResponse));
            }

            String sql = "select o.deptName,substr(q.prtLevelCode,length(q.prtLevelCode)-1) prtSeq,q.prtName,q.dictCode,q.dictValue,q.dictName,sum(case when ru.id is not null then 1 else 0 end) cnt from " +
                    "(select id ,name deptName,levelCode deptLevelCode from tbl_org o where o.org_type='1') o cross join " +
                    "(select p.name prtName,p.code prtCode,p.levelCode prtLevelCode,d.id dictId,d.code dictCode,d.name dictName,d.levelCode dictLevelCode,d.value dictValue " +
                    "   from tbl_dict d left join tbl_dict p on p.id = d.parent_id left join tbl_dict gp on gp.id = p.parent_id where gp.code=:wjCode" +
                    ") q " +
                    "left join " +
                    "(select ri.*,u.mobile,u.deptId from app_wj_result_item ri left join app_user u on ri.userId = u.id where ri.wjCode=:wjCode and ri.createtime>=:startTime and ri.createtime<=:endTime) ru on LOCATE(q.dictCode,ru.answCode)>0 and ru.deptId = o.id " +
                    " group by o.id,q.dictCode " +
                    " order by o.deptLevelCode asc,q.prtLevelCode asc,q.dictLevelCode asc";
            Map<String, Object> params = new HashMap<>();
            params.put("startTime",startTime);
            params.put("endTime",endTime);
            params.put("wjCode",wjCode);

            List<Map<String, Object>> dataList = findMapBySql(sql, params);

            if (dataList == null || dataList.size() == 0) {
                baseResponse.setStatus(1);
                baseResponse.setMessage("无数据！");
                response.getWriter().write(new Gson().toJson(baseResponse));
                return;
            }
            //excel标题
            String[] title = {"部门","序号", "题目", "选项序号", "选项", "数量"};
            //excel文件名
            String fileName = "按部门统计" + DateUtil.format(new Date(), "yyyyMMddHHmmss") + ".xlsx";
            //sheet名
            String sheetName = "统计";

            String content[][] = new String[dataList.size()][title.length];

            for (int i = 0; i < dataList.size(); i++) {
                Map<String, Object> map = dataList.get(i);
                content[i][0] = map.get("deptName") == null ? "" : map.get("deptName").toString();
                content[i][1] = map.get("prtSeq") == null ? "" : map.get("prtSeq").toString();
                content[i][2] = map.get("prtName") == null ? "" : map.get("prtName").toString();
                content[i][3] = map.get("dictValue") == null ? "" : map.get("dictValue").toString();
                content[i][4] = map.get("dictName") == null ? "" : map.get("dictName").toString();
                content[i][5] = map.get("cnt") == null ? "" : map.get("cnt").toString();


            }
            SXSSFWorkbook wb = Excel07ExportUtil.getSXSSWorkbook(sheetName, title, content, null);
            DownloadUtils.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        }catch (Exception ex){
            ex.printStackTrace();
        }
    }


    @Override
    public BaseResponse getWjUserDAByDept(String wjCode, String startTime, String endTime) throws Exception {


        if (StringUtils.isEmpty(startTime)) {
            startTime = "2024-06-20 00:00:00";
        }

        if (StringUtils.isEmpty(endTime)) {
            endTime = DateUtil.format(new Date(),DateUtil.YYYY_MM_DD)+" 23:59:59";
        }

        BaseResponse baseResponse = new BaseResponse();

        String sql = "select o.name deptName,ifnull(pcnt,0) pcnt,ifnull(cnt,0) cnt from " +
                "(select id,name,code,levelCode from tbl_org where org_type = '1') o left join" +
                "(" +
                "   select deptId,count(*) pcnt" +
                "   sum(case when r.id is not null then 1 else 0 end) cnt ,"+
                "   from app_user u left join " +
                "   (select * from app_wj_result where createtime>=:startTime and createtime<=:endTime) r on r.userId = u.id  group by u.deptId" +
                ") u on u.deptId = o.id" +
                " order by o.levelCode asc";
        Map<String, Object> params = new HashMap<>();
        params.put("startTime",startTime);
        params.put("endTime",endTime);
        if (StringUtils.isNotEmpty(wjCode)) {
            sql = "select o.name deptName,ifnull(pcnt,0) pcnt,ifnull(cnt,0) cnt from " +
                    "(select id,name,code,levelCode from tbl_org where org_type = '1') o left join" +
                    "(" +
                    "   select deptId,count(*) pcnt," +
                    "   sum(case when r.id is not null then 1 else 0 end) cnt" +
                    "   from app_user u left join " +
                    "   (select * from app_wj_result where wjCode = :wjCode and createtime>=:startTime and createtime<=:endTime) r on r.userId = u.id  group by u.deptId" +
                    ") u on u.deptId = o.id" +
                    " order by o.levelCode asc";
            params.put("wjCode",wjCode);
        }

        List<Map<String, Object>> list = findMapBySql(sql, params);
        baseResponse.setData(list);
        return baseResponse;
    }

    @Override
    public void exportWjResultDAInOne(String token, String wjCode, String startTime, String endTime, HttpServletRequest request, HttpServletResponse response) {
        try {

            response.setCharacterEncoding("UTF-8");
            //设置ContentType字段值
            response.setContentType("text/html;charset=utf-8");

            BaseResponse baseResponse = checkEmpToken(token);
            if (baseResponse.getStatus() != 0) {
                response.getWriter().write(new Gson().toJson(baseResponse));
                return;
            }

            if (StringUtils.isEmpty(wjCode)) {
                baseResponse.setStatus(1);
                baseResponse.setMessage("参数缺失");
                response.getWriter().write(new Gson().toJson(baseResponse));
                return;
            }

            String basePath = request.getSession().getServletContext().getRealPath("/");
            String sourcePath = PropertiesUtil.getValue("officePath.model");
            String templatePath = basePath + sourcePath + "template_all.xlsx";
            File templateFile = new File(templatePath);
            if (!templateFile.exists()) {
                baseResponse.setStatus(1);
                baseResponse.setMessage("模板不存在");
                response.getWriter().write(new Gson().toJson(baseResponse));
                return;
            }


            String filePrefixFormat = "yyyyMMddHHmmssS";
            String fileName = "江苏省机关调查问卷统计表" + DateUtil.format(new Date(), filePrefixFormat) + ".xlsx";


            XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(templatePath));
            //SXSSFWorkbook wb = new SXSSFWorkbook(wbTemplate,200);
            String sql = "select o.name,ifnull(cnt ,0) cnt from tbl_org o left join(" +
                    "   select u.deptId,count(*) cnt from app_wj_result r left join app_user u on r.userId = u.id where r.createtime>=:startTime and r.createtime<=:endTime group by u.deptId )a " +
                    "on o.id=a.deptId " +
                    "where length(o.levelCode)=12 order by o.levelCode asc";

            Map<String, Object> params = new HashMap<>();
            params.put("startTime",startTime);
            params.put("endTime",endTime);
            List<Map<String, Object>> orgCntList = findMapBySql(sql,params);


            sql = "select substr(q.prtLevelCode,length(q.prtLevelCode)-1) prtSeq,q.prtName,q.dictCode,q.dictValue,q.dictName,sum(case when ru.id is not null then 1 else 0 end) cnt from " +
                    "(" +
                    "   select p.name prtName,p.code prtCode,p.levelCode prtLevelCode,d.id dictId,d.code dictCode,d.name dictName,d.levelCode dictLevelCode,d.value dictValue " +
                    "   from tbl_dict d left join tbl_dict p on p.id = d.parent_id left join tbl_dict gp on gp.id = p.parent_id where gp.code=:wjCode " +
                    ") q " +
                    "left join " +
                    "(" +
                    "   select ri.* from app_wj_result_item ri where ri.wjCode=:wjCode and ri.createtime>=:startTime and ri.createtime<=:endTime" +
                    ") ru on LOCATE(q.dictCode,ru.answCode)>0 " +
                    "group by q.dictCode " +
                    "order by q.prtLevelCode asc,q.dictLevelCode asc";
            params.put("wjCode",wjCode);
            List<Map<String, Object>> optionCntList = findMapBySql(sql, params);


            sql = "select substr(q.prtLevelCode,length(q.prtLevelCode)-1) prtSeq,q.prtName,q.dictCode,q.dictValue,q.dictName,sum(case when ru.id is not null then 1 else 0 end) cnt from " +
                    "(" +
                    "   select p.name prtName,p.code prtCode,p.levelCode prtLevelCode,d.id dictId,d.code dictCode,d.name dictName,d.value dictValue,d.levelCode dictLevelCode from tbl_dict d left join tbl_dict p on p.id = d.parent_id left join tbl_dict gp on gp.id = p.parent_id where gp.code='wjlb_swj24s3'" +
                    ") q " +
                    " left join " +
                    "(" +
                    "   select ri.*,u.mobile,u.deptId from app_wj_result_item ri left join app_user u on ri.userId = u.id left join tbl_org o on o.id=u.deptId where ri.wjCode=:wjCode and ri.createtime>=:startTime and ri.createtime<=:endTime and o.name=:orgName " +
                    ") ru on LOCATE(q.dictCode,ru.answCode)>0 " +
                    "group by q.dictCode " +
                    "order by q.prtLevelCode asc,q.dictLevelCode asc";
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                Sheet sheetAt = wb.getSheetAt(i);
                String sheetName = sheetAt.getSheetName();
                logger.error("SheetName:"+ sheetName);

                if ("总体统计".equals(sheetName)) {

                    /*for (int j = 0; j < orgCntList.size(); j++) {
                        Map<String, Object> orgCnt = orgCntList.get(j);
                        Row row = sheetAt.getRow(j);
                        logger.error("Row:"+ row);
                        logger.error("Cell1:"+ row.getCell(1).getStringCellValue());
                        Cell cell2 = row.getCell(2);
                        cell2.setCellValue(orgCnt.get("cnt").toString());
                    }*/
                    for (int j = 0; j < optionCntList.size(); j++) {
                        Map<String, Object> optionCnt = optionCntList.get(j);
                        Row row = sheetAt.getRow(j+1);
                        logger.error("Cell7:"+ row.getCell(7).getStringCellValue());
                        Cell cell8 = row.getCell(8);
                        cell8.setCellValue(optionCnt.get("cnt").toString());

                        if (j<orgCntList.size()) {
                            Map<String, Object> orgCnt = orgCntList.get(j);
                            Cell cell2 = row.getCell(2);
                            cell2.setCellValue(orgCnt.get("cnt").toString());
                        }
                    }

                }else if ("意见建议".equals(sheetName)) {

                }else {
                    params.put("orgName",sheetName);
                    List<Map<String, Object>> orgOptionCntList = findMapBySql(sql, params);
                    for (int j = 0; j < orgOptionCntList.size(); j++) {
                        Map<String, Object> orgOptionCnt = orgOptionCntList.get(j);
                        Row row = sheetAt.getRow(j + 1);
                        Cell cell4 = row.getCell(4);
                        cell4.setCellValue(orgOptionCnt.get("cnt").toString());
                    }
                }
            }

            DownloadUtils.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();

            /*String sql = "select o.deptName,substr(q.prtLevelCode,length(q.prtLevelCode)-1) prtSeq,q.prtName,q.dictCode,q.dictValue,q.dictName,sum(case when ru.id is not null then 1 else 0 end) cnt from " +
                    "(select id ,name deptName,levelCode deptLevelCode from tbl_org o where o.org_type='1') o cross join " +
                    "(select p.name prtName,p.code prtCode,p.levelCode prtLevelCode,d.id dictId,d.code dictCode,d.name dictName,d.levelCode dictLevelCode,d.value dictValue " +
                    "   from tbl_dict d left join tbl_dict p on p.id = d.parent_id left join tbl_dict gp on gp.id = p.parent_id where gp.code=:wjCode" +
                    ") q " +
                    "left join " +
                    "(select ri.*,u.mobile,u.deptId from app_wj_result_item ri left join app_user u on ri.userId = u.id where ri.wjCode=:wjCode and ri.createtime>=:startTime and ri.createtime<=:endTime) ru on LOCATE(q.dictCode,ru.answCode)>0 and ru.deptId = o.id " +
                    " group by o.id,q.dictCode " +
                    " order by o.deptLevelCode asc,q.prtLevelCode asc,q.dictLevelCode asc";
            Map<String, Object> params = new HashMap<>();
            params.put("startTime",startTime);
            params.put("endTime",endTime);
            params.put("wjCode",wjCode);

            List<Map<String, Object>> dataList = findMapBySql(sql, params);

            if (dataList == null || dataList.size() == 0) {
                baseResponse.setStatus(1);
                baseResponse.setMessage("无数据！");
                response.getWriter().write(new Gson().toJson(baseResponse));
                return;
            }
            //excel标题
            String[] title = {"部门","序号", "题目", "选项序号", "选项", "数量"};
            //excel文件名
            String fileName = "按部门统计" + DateUtil.format(new Date(), "yyyyMMddHHmmss") + ".xlsx";
            //sheet名
            String sheetName = "统计";

            String content[][] = new String[dataList.size()][title.length];

            for (int i = 0; i < dataList.size(); i++) {
                Map<String, Object> map = dataList.get(i);
                content[i][0] = map.get("deptName") == null ? "" : map.get("deptName").toString();
                content[i][1] = map.get("prtSeq") == null ? "" : map.get("prtSeq").toString();
                content[i][2] = map.get("prtName") == null ? "" : map.get("prtName").toString();
                content[i][3] = map.get("dictValue") == null ? "" : map.get("dictValue").toString();
                content[i][4] = map.get("dictName") == null ? "" : map.get("dictName").toString();
                content[i][5] = map.get("cnt") == null ? "" : map.get("cnt").toString();


            }
            SXSSFWorkbook wb = Excel07ExportUtil.getSXSSWorkbook(sheetName, title, content, null);
            DownloadUtils.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();*/
        }catch (Exception ex){
            ex.printStackTrace();
        }
    }
}
